Converting a String to HEX in SQL

Question

I'm looking for a way to transform a genuine string into it's hexadecimal value in SQL. I'm looking something that is Informix-friendly but I would obviously prefer something database-neutral

Here is the select I am using now:

SELECT SomeStringColumn from SomeTable

Here is the select I would like to use: SELECT hex( SomeStringColumn ) from SomeTable

Unfortunately nothing is that simple... Informix gives me that message: Character to numeric conversion error

Any idea?

Answer 1

Can you use Cast and the fn_varbintohexstr?

SELECT master.dbo.fn_varbintohexstr(CAST(SomeStringColumn AS varbinary)) 
FROM SomeTable

I'm not sure if you have that function in your database system, it is in MS-SQL.

I just tried it in my SQL server MMC on one of my tables:

SELECT     master.dbo.fn_varbintohexstr(CAST(Addr1 AS VARBINARY)) AS Expr1
FROM         Customer

This worked as expected. possibly what I know as master.dbo.fn_varbintohexstr on MS-SQL, might be similar to informix hex() function, so possibly try:

SELECT     hex(CAST(Addr1 AS VARBINARY)) AS Expr1
FROM         Customer

Answer 2

The following works in Sql 2005.

select convert(varbinary, SomeStringColumn) from SomeTable