12

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?

Claude Houle
  • 41,064
  • 8
  • 33
  • 42
  • 1
    The HEX function is to convert an INTEGER (or INT8 or BIGINT) to a hex string. What are you seeking as the output from HEX_STRING("xyz")? – Jonathan Leffler Oct 21 '08 at 15:35
  • Also, it is courteous to select an answer - or if nothing is answering your question, it is sensible to edit your question so it can be understood. You should be aiming to select a best answer - please. – Jonathan Leffler Oct 24 '08 at 23:33
  • 1
    You should reword your question. Obviously you don't want to interpret the string as a number ("12" -> 12). It seems you want to convert the underlying bytes to hex. ("abc" -> hex). Do you want ASCII codes, Unicode, something else? Be specific. – colithium Jun 28 '09 at 03:09

7 Answers7

21

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
C B
  • 1,677
  • 6
  • 18
  • 20
stephenbayer
  • 12,373
  • 15
  • 63
  • 98
  • 2
    I think you answered the question he meant to ask. +1 – colithium Jun 28 '09 at 03:09
  • 2
    Even in MSSQL this function is not supported/documented. Use of the function is not recommended if you require compatibility with future versions of MS SQL server – Faiz Sep 01 '09 at 12:46
9

The following works in Sql 2005.

select convert(varbinary, SomeStringColumn) from SomeTable
jhamm
  • 1,858
  • 21
  • 19
  • Spot on. This should be the selected answer. – the.jxc Oct 29 '10 at 02:46
  • This produces a varbinary result, which is presented in the result view in SSMS as a hex string like 0x680065006C006C006F002C0077006F0072006C006400 -- but it is still a binary value, and other programs will not see it as a string. – Ross Presser Jul 02 '14 at 16:51
  • Furthermore, the user asked about Informix, not SQL Server. – Ross Presser Jul 02 '14 at 16:53
1

Try this:

select convert(varbinary, '0xa3c0', 1)

The hex number needs to have an even number of digits. To get around that, try:

select convert(varbinary, '0x' +  RIGHT('00000000' + REPLACE('0xa3c','0x',''), 8), 1)
Ben
  • 51
  • 2
0

If it is possible for you to do this in the database client in code it might be easier.

Otherwise the error probably means that the built in hex function can't work with your values as you expect. I would double check the input value is trimmed and in the format first, it might be that simple. Then I would consult the database documentation that describes the hex function and see what its expected input would be and compare that to some of your values and find out what the difference is and how to change your values to match that of the expected input.

A simple google search for "informix hex function" brought up the first result page with the sentence: "Must be a literal integer or some other expression that returns an integer". If your data type is a string, first convert the string to an integer. It looks like at first glance you do something with the cast function (I am not sure about this).

select hex(cast SomeStringColumn as int)) from SomeTable
Josh
  • 17,834
  • 7
  • 50
  • 68
0

OLD Post but in my case I also had to remove the 0x part of the hex so I used the below code. (I'm using MS SQL)

convert(varchar, convert(Varbinary(MAX), YOURSTRING),2)

gusmundo
  • 1
  • 2
0
SUBSTRING(CONVERT(varbinary,Addr1 ) ,1,1) as Expr1
James Risner
  • 5,451
  • 11
  • 25
  • 47
Евген Марчен
  • 1
0

what about:

declare @hexstring varchar(max);
set @hexstring = 'E0F0C0';
select cast('' as xml).value('xs:hexBinary( substring(sql:variable("@hexstring"), sql:column("t.pos")) )', 'varbinary(max)')
from (select case substring(@hexstring, 1, 2) when '0x' then 3 else 0 end) as t(pos)

I saw this here: http://blogs.msdn.com/b/sqltips/archive/2008/07/02/converting-from-hex-string-to-varbinary-and-vice-versa.aspx

Sorrry, that work only on >MS SQL 2005

Boklucius
  • 1,896
  • 17
  • 19