## Thursday, May 21, 2009

### Teradata tip of the day

Hi Friends,

I thought of sharing some of the nuances I learn in teradata as a part of my project with you.
Hope this initiative helps :)

While exploring on how to convert hexadecimal value to interger and viceversa, my team was not able to find a direct function on Teradata.

This is the alternative we used (a hack :)) -

SELECT
1234567890 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
;

Try it out. Also, if you guys know any direct method, let me know.

I keep this initative to a closed group of friends working in DW, so we can share each other stuff we know and broaden our knowledge on TD :)

This chain can be used for learning any new technical info.

~Meena

1. 2. 3. 