Converting string data to hex. Cannot cast varchar to varbinary

Hi everyone,

I have a prospect that wants to replicate in Vertica the hex() function included in their current db (Infobright, that uses this same function as mysql) and they are trying to convert strings to hexadecimal from a varchar column.

I thought that using our "to_hex" function could do it, but it seems that we only support number input. But I found something interesting.

If I do "select to_hex('string'::varbinary);" it works. But if the string is selected from a varchar column it cannot cast the variable (we cannot cast varchar to binary or varbinary). Here is an example executed from my vsql:

dbadmin=> select node_name from nodes;

node_name

v_vmart_node0001
(1 row)

dbadmin=> select to_hex('v_vmart_node0001'::varbinary);

to_hex

765f766d6172745f6e6f646530303031
(1 row)

dbadmin=> select to_hex(node_name::varbinary) from nodes;
ERROR 2366: Cannot cast type varchar to varbinary

Would be an easy way to convert a string from a varchar column to hexadecimal without developing a UDx?

Thanks!

Best regards,
David.