does any one know how to covert varbyte to hex string?

General
Fan

does any one know how to covert varbyte to hex string?

hi all

 

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?

3 REPLIES
Junior Contributor

Re: does any one know how to covert varbyte to hex string?

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
Senior Apprentice

Re: does any one know how to covert varbyte to hex string?

Hi,

 

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?

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Fan

Re: does any one know how to covert varbyte to hex string?

Thanks dnoeth and Dave

 

use FROM_BYTES seems is work for my purpose and i still trying, thanks you two!!