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]

Re: SQL function for HEX input

On Feb 5, 8:36 am, globomike <M_Tiefenbac... @gmx.dewrote:

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*in t(substr(s,1,1) ) + int(substr(s,
2,1))) as varchar(100)), 0)
union all
select rtrim(res) ||
chr(16*int(subs tr(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