integer to hex

[naheek]

I am trying to find a way to convert an integer value to hex in SQL. I have found the Teradata function chartohexint, but then I need to convert from int to char then from char to hex. When I do this somehow I end up with non 1 to 1 mapping between integers and the hex equivalents. Does anyone have any ideas how to do this?
(Scratches head looking bemused...)

I would like to stay in SQL, but if there is someway to code a function, how would that be done???

Thanks

 

[naheek]

I know it is a cheap hack, but I figured out how to do this fairly easily. Since I was only trying to convert 0 to 65535 dec to hex, I created an excel spreadsheet with these values in col1 and dec2hec(col1) in col2. Extended this to 65535 records and then loaded this table into Teradata. Now, I can use this table as a lookup for my dec value to find my hex value.

I know...I should be ashamed, but it got the job done!

Cheers,

 

[dnoeth]

You can do it without join, but it's a hack, too ;-)

Dieter

SELECT
123456789 AS x

,SUBSTRING('0123456789ABCDEF' FROM x / (16**7) MOD 16 + 1 FOR 1) ||
SUBSTRING('0123456789ABCDEF' FROM x / (16**6) MOD 16 + 1 FOR 1) ||
SUBSTRING('0123456789ABCDEF' FROM x / (16**5) MOD 16 + 1 FOR 1) ||
SUBSTRING('0123456789ABCDEF' FROM x / (16**4) MOD 16 + 1 FOR 1) ||
SUBSTRING('0123456789ABCDEF' FROM x / (16**3) MOD 16 + 1 FOR 1) ||
SUBSTRING('0123456789ABCDEF' FROM x / (16**2) MOD 16 + 1 FOR 1) ||
SUBSTRING('0123456789ABCDEF' FROM x / (16**1) MOD 16 + 1 FOR 1) ||
SUBSTRING('0123456789ABCDEF' FROM x / (16**0) MOD 16 + 1 FOR 1) AS Int2HexString

,Int2HexString as s

,POSITION(SUBSTRING(s FROM 8 FOR 1) IN '123456789ABCDEF') * ((16**0) (INT)) +
POSITION(SUBSTRING(s FROM 7 FOR 1) IN '123456789ABCDEF') * ((16**1) (INT)) +
POSITION(SUBSTRING(s FROM 6 FOR 1) IN '123456789ABCDEF') * ((16**2) (INT)) +
POSITION(SUBSTRING(s FROM 5 FOR 1) IN '123456789ABCDEF') * ((16**3) (INT)) +
POSITION(SUBSTRING(s FROM 4 FOR 1) IN '123456789ABCDEF') * ((16**4) (INT)) +
POSITION(SUBSTRING(s FROM 3 FOR 1) IN '123456789ABCDEF') * ((16**5) (INT)) +
POSITION(SUBSTRING(s FROM 2 FOR 1) IN '123456789ABCDEF') * ((16**6) (INT)) +
POSITION(SUBSTRING(s FROM 1 FOR 1) IN '123456789ABCDEF') * ((16**7) (INT)) AS HexString2Int
;