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

Quite good. Thanks a lot.

ReplyDeleteThanks for sharing the info.

ReplyDeleteHi Meena

ReplyDeleteI need to load current date through fast load. pls need your help at the earliest.