Archives of the TeradataForum

Message Posted: Wed, 10 Sep 2003 @ 08:07:45 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Anomy Anom wrote:

A User Defined Function in V2R5.1 ;-)

As there's no R5.1 yet and i don't know exactly what you mean by "binary", this is how to convert an Int to a Hex-String using SQL:

SELECT
   123456789 AS intcol

  ,SUBSTRING('0123456789ABCDEF' FROM intcol / 268435456 MOD 16 + 1 FOR 1) ||
   SUBSTRING('0123456789ABCDEF' FROM intcol /  16777216 MOD 16 + 1 FOR 1) ||
   SUBSTRING('0123456789ABCDEF' FROM intcol /   1048576 MOD 16 + 1 FOR 1) ||
   SUBSTRING('0123456789ABCDEF' FROM intcol /     65536 MOD 16 + 1 FOR 1) ||
   SUBSTRING('0123456789ABCDEF' FROM intcol /      4096 MOD 16 + 1 FOR 1) ||
   SUBSTRING('0123456789ABCDEF' FROM intcol /       256 MOD 16 + 1 FOR 1) ||
   SUBSTRING('0123456789ABCDEF' FROM intcol /        16 MOD 16 + 1 FOR 1) ||
   SUBSTRING('0123456789ABCDEF' FROM intcol             MOD 16 + 1 FOR 1) AS Int2HexString
;

or

SELECT
   123456789 AS intcol

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

Dieter

	<Prev	Next>		<<First	<Prev	Next>	Last>>