SQL function for HEX input

[globomike]

Hi,

I am looking for an SQL function which does exactly the same like the
x'<hex-string>' in an insert.

Background:
I want to "translate" a hexadecimal string (not number) with a length
of 32 into a CHAR (16) FOR BIT DATA
in a way that the hex() function of that string returns the original
(hex) text.

The function should be able to handle hex(00) exactly the same way the
x'<hex-string>' does it.
Any ideas?

Thanks in advance

Michael

[Lennart]

On Feb 5, 8:36 am, globomike <[e-mail]:

Hi,

I am looking for an SQL function which does exactly the same like the
x'<hex-string>' in an insert.

Background:
I want to "translate" a hexadecimal string (not number) with a length
of 32 into a CHAR (16) FOR BIT DATA
in a way that the hex() function of that string returns the original
(hex) text.

The function should be able to handle hex(00) exactly the same way the
x'<hex-string>' does it.

Any ideas?

Thanks in advance

Michael

I don't think there is a predefined function doing that. On the other
hand you can easily create one your self. Something along the lines
of:

create function unhex (s varchar(100))
returns varchar(100)
return
with iter (res, n) as (
values (cast(chr(16*int(substr(s,1,1)) + int(substr(s,
2,1))) as varchar(100)), 0)
union all
select rtrim(res) ||
chr(16*int(substr(s,n+3,1)) + int(substr(s,n
+4,1))), n+2
from iter
where n+2 < length(s) and n < 100

) select res from iter
where n = (select max(n) from iter) @

Not tested ;-)
HTH
/Lennart