How to get ASCII Value of Character

UDA
Fan

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
9 REPLIES
rgs
Enthusiast

Re: How to get ASCII Value of Character

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)
--------------
?

Enthusiast

Re: How to get ASCII Value of Character


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 ASCIIVAL
FROM MYTABLE ;
Fan

Re: How to get ASCII Value of Character

Thanks folks

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

Re: How to get ASCII Value of Character

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

Re: How to get ASCII Value of Character

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
Enthusiast

Re: How to get ASCII Value of Character

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 asciival
from mytable;

*** Query completed. 12 rows found. 2 columns returned.
*** Total elapsed time was 1 second.

col1 asciival
---- -----------
A 65
B 66
C 67
D 68
E 69
F 70
G 71
H 72
I 73
J 74
K 75
L 76
Enthusiast

Re: How to get ASCII Value of Character

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

Re: How to get ASCII Value of Character

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

Fan

Re: How to get ASCII Value of Character

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.