in my source table have a column stored value with varbyte and example value is 0xc5304e8be9030080
is possible direct transfer from vaybyte to hex string ex. "0xc5304e8be9030080"?
i googled some result maybe can use UDF achieve, but i don't know the detail of UDF, so may i have any suggestion or idea on this purpose?
There from_bytes & to_bytes:
select from_bytes('00'xb||'c5304e8be9030080'xb, 'base16') C5304E8BE9030080
Don't forget to add the binary zero, otherwise it will return an "negative" hex string when the first byte is greater than 7f:
select from_bytes(c5304e8be9030080'xb, 'base16') -3ACFB17416FCFF80
You may be able to use the FROM_BYTES function (I'm not sure which release of TD introduced this or what release you're on) but that largely depends on your data. Here is a simple example:
CREATE SET VOLATILE TABLE vt1 (picol INTEGER ,datacol VARBYTE(20) ) UNIQUE PRIMARY INDEX(picol) ON COMMIT PRESERVE ROWS; INSERT INTO vt1 VALUES(1,'3233606162'xb); SELECT vt1.* ,FROM_BYTES(datacol,'base16') FROM vt1;
This returns the value '3233606162' as a character string.
Is that what you're after?