May 16

1 comments

Find out the ASCII-Code of a Character in Teradata SQL

By Roland Wenzlofsky

May 16, 2014

ASCII, CHAR2HEXINT, sql

If you ever had the need to find out the ASCII-Code of a character, you will know that no function in Teradata SQL exists to solve this problem.

Here is a query workaround which solves this issue. What below SQL statement does is the following:

The CHAR2HEXINT Teradata SQL function returns a hexadecimal value; we need to convert it into the decimal number system. Conversion is done by taking the first part of the returned hexadecimal value multiplied by 16 and adding the second part of the hexadecimal value.

SELECT
CASE SUBSTRING(CHAR2HEXINT(‘B') FROM 3 FOR 1)
WHEN ‘0' THEN 0
WHEN ‘1' THEN 1
WHEN ‘2' THEN 2
WHEN ‘3' THEN 3
WHEN ‘4' THEN 4
WHEN ‘5' THEN 5
WHEN ‘6' THEN 6
WHEN ‘7' THEN 7
WHEN ‘8' THEN 8
WHEN ‘9' THEN 9
WHEN ‘A' THEN 10
WHEN ‘B' THEN 11
WHEN ‘C' THEN 12
WHEN ‘D' THEN 13
WHEN ‘E' THEN 14
WHEN ‘F' THEN 15
END * 16 +
CASE SUBSTRING(CHAR2HEXINT(‘B') FROM 4 FOR 1)
WHEN ‘0' THEN 0
WHEN ‘1' THEN 1
WHEN ‘2' THEN 2
WHEN ‘3' THEN 3
WHEN ‘4' THEN 4
WHEN ‘5' THEN 5
WHEN ‘6' THEN 6
WHEN ‘7' THEN 7
WHEN ‘8' THEN 8
WHEN ‘9' THEN 9
WHEN ‘A' THEN 10
WHEN ‘B' THEN 11
WHEN ‘C' THEN 12
WHEN ‘D' THEN 13
WHEN ‘E' THEN 14
WHEN ‘F' THEN 15
END

Roland Wenzlofsky


Roland Wenzlofsky is a graduated computer scientist and Data Warehouse professional working with the Teradata database system for more than 20 years. He is experienced in the fields of banking and telecommunication with a strong focus on performance optimization.

You might also like

  • Avatar
    Beat Brüngger says:

    Hi, if you are on Teradata >= 14.00 you can try:
    SELECT ASCII(‘A’);
    ->> Returns the decimal representation of the first character in string_expr as a NUMBER value.

    Reply

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Never miss a good story!

     Subscribe to our newsletter to keep up with the latest trends!

    >