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)-------------- ?
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 ;
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...
I don't think the second method works. Try the character 'J' for example. The hex '4A' can't be converted to an integer.
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
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
Oops you are right, it doesn't work for certain character values because of non integers in the o/p ... my bad ! :-)
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