All Forums UDA
MTariq 3 posts Joined 06/07
07 Jun 2007
How to get ASCII Value of Character

I need ASCII value of characters, e.g. A=65 , B=66.I have tried Select CHR2HEXINT('A') which returns me the HEX value (0041) of the char passed as parameter.Anyone has any idea how to convert HEX (0041) to ASCII(65) using FORMAT or some other built-in function.Thanks

rgs 106 posts Joined 02/07
07 Jun 2007

I don't know of a built in function or format, but a UDF can be written to do that (5.1 and above):---------------------------------------------------------------#define SQL_TEXT Latin_Text#include "sqltypes_td.h"/* CREATE statement: REPLACE FUNCTION char2int(VARCHAR(1)) RETURNS BYTEINT LANGUAGE C NO SQL RETURNS NULL ON NULL INPUT PARAMETER STYLE TD_GENERAL EXTERNAL; Assumes this source in in current directory. Change to not protected mode to run at least 4 to 10 times faster: ALTER FUNCTION char2int EXECUTE NOT PROTECTED; needs GRANT ALTER FUNCTION privilege to execute ALTER statement *//* real simple */void char2int(VARCHAR_LATIN *chr, BYTEINT *result, char sqlstate[6]){ *result = *chr;}---------------------------------------------------- BTEQ -- Enter your DBC/SQL request or BTEQ command: select char2int('A'); *** Query completed. One row found. One column returned. *** Total elapsed time was 1 second.char2int('A')------------- 65 BTEQ -- Enter your DBC/SQL request or BTEQ command: select char2int('J'); *** Query completed. One row found. One column returned. *** Total elapsed time was 1 second.char2int('J')------------- 74 BTEQ -- Enter your DBC/SQL request or BTEQ command: select char2int(NULL); *** Query completed. One row found. One column returned. *** Total elapsed time was 1 second.char2int(Null)-------------- ?

joedsilva 505 posts Joined 07/05
08 Jun 2007

Assuming we are in the ASCII limit (0-256)SELECT COL1, ((CHAR2HEXINT(COL1) (INTEGER) (NAMED HVAL))/100*16*16 + (HVAL MOD 100)/10 * 16 + (HVAL MOD 10)) AS ASCIIVALFROM MYTABLE ;

MTariq 3 posts Joined 06/07
08 Jun 2007

Thanks folksThe second option for worked better for me at this time because I did not have to after DBAs to create the UDF functions...

rgs 106 posts Joined 02/07
08 Jun 2007

I don't think the second method works. Try the character 'J' for example. The hex '4A' can't be converted to an integer.

MTariq 3 posts Joined 06/07
08 Jun 2007

It does not work between J and O (both upper and lower case) and Zz.I think if one has to use it more often either create a table for HEXtoASCII values and do lookup on that table. For that matter it can be CHARtoASCII table. It is one time INSERT for the all ASCII values but can be reused.Or, just create the UDF function once and for all. Anyways, I was only looking for a short term solution at this time. Thanks

rgs 106 posts Joined 02/07
08 Jun 2007

Here is a non UDF solution: case substring(char2hexint(col1) from 1 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(col1) from 2 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 as asciivalfrom mytable; *** Query completed. 12 rows found. 2 columns returned. *** Total elapsed time was 1 second.col1 asciival---- -----------A 65B 66C 67D 68E 69F 70G 71H 72I 73J 74K 75L 76

joedsilva 505 posts Joined 07/05
09 Jun 2007

Oops you are right, it doesn't work for certain character values because of non integers in the o/p ... my bad ! :-)

enjoycoding 20 posts Joined 08/10
19 Oct 2010

Hi,

Assuming only the last two characters are useful of CHAR2HEXINT function output, please find other option of getting the ascii value.

SELECT 'a' AS reqchar,
(((SUBSTRING(CHAR2HEXINT(reqchar),3,1)) (INTEGER) )* 16 )+
((SUBSTRING(CHAR2HEXINT(reqchar),4,1)) (INTEGER)) AS asciival

bebr 1 post Joined 12/11
28 Apr 2015

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

You must sign in to leave a comment.