HELP!!! Converting Hexadecimal to Decimal or vice

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

HELP!!! Converting Hexadecimal to Decimal or vice

Author

Topic

KRichradson1127
Starting Member

20 Posts

Posted - 2009-07-28 : 14:58:03

I'm in the process of converting two columns in two separate tables...

One column in Table A has a hexadecimal number (with a nvarchar data type) i.e. "A5000000020" and converting it to a decimal it should be "11338713661472"...

In Table B the column has a decimal number (with a bigint data type) i.e "1434519076864" and converting it to a hexadecimal the would make the value "9D500000005"

I keep getting stuck!!!

How can I successfully convert these columns?!

Thanks in advance!!!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-28 : 15:25:31

Google is your friend, from:[url]http://classicasp.aspfaq.com/general/how-do-i-convert-from-hex-to-int-and-back.html[/url]

SELECT 'Int -> Hex' 
 
SELECT CONVERT(VARBINARY(8), 16777215) 
 
SELECT 'Hex -> Int' 
 
SELECT CONVERT(INT, 0xFFFFFF)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-28 : 15:32:20

The problem is that you are storing the hexadecimal value as a string (in a nvarchar column). It is not actually binary so you can't treat it as such.

One way to get your first result is:


declare @nv nvarchar(50)
       ,@execStr nvarchar(2000)
set @nv = 'A5000000020'

set @execStr = 'select convert(bigint, 0x' + @nv + ')'
exec(@execStr)

OUTPUT:
--------------------
11338713661472

Your second issue (bigint: 1434519076864)
I'm not sure how you are arriving at 9D500000005

Be One with the Optimizer
TG

EDIT:
if you want to store hex data I suggest you store it in a binary, varbinary, or image datatype.

If you need to convert from hex to a string representation of hex then sql has a built in function to do so:
sys.fn_varbintohexstr

To go the other direction you need a custom one. I believe there have been several posted here...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-28 : 16:03:09

Also try out XQuery
http://blogs.msdn.com/sqltips/archive/2008/07/02/converting-from-hex-string-to-varbinary-and-vice-versa.aspx

N 56°04'39.26"
E 12°55'05.63"

KRichradson1127
Starting Member

20 Posts

Posted - 2009-07-30 : 15:03:05

Thanks TG, it pulled the data I needed!

Is there a way that this script can pull more than one row? I added the BOLD select query into this script, but I keep getting the error message stating:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

declare @nv nvarchar(50)
,@execStr nvarchar(2000)
set @nv = (select application.client_id from [application] where application_id = application_id)
set @execStr = 'select convert(bigint, 0x' + @nv + ')'
exec(@execStr)

Any suggestions as to how I can return multiple rows?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-30 : 16:32:03

three ways - one would be to look into Peso's suggestion: XQuery. Another would be to loop through your values to get them one at a time as above. Another could be to use a function as I mentioned earlier. It would be the reverse of the built-in one sys.fn_varbintohexstr. Here is one I wrote some time ago if you can't find anything better:


if object_id('dbo.fn_hexStrToVarbin') > 0
	drop function dbo.fn_hexStrToVarbin
go

create function dbo.fn_hexStrToVarbin (@s varchar(8000))
returns varbinary(4000)

/*
this function takes a hexidecimal string, converts, and returns it as a varbinary(4000)
sample call:

select	dbo.fn_hexStrToVarbin(sHex)
from	(
	select	'0x000000002FDAF785' sHex union all
	select '0x0A' union all
	select '0x060A' union all
	select '' union all
	select '0x06'
	union all select replicate('FF', 3999) + 'AA'
	) a

--works for nvarchar(4000) too
select dbo.fn_hexStrToVarbin(N'' + replicate('FF', 1999) + 'AA')
*/
as
begin
       declare @out varbinary(4000)
              ,@n int
              ,@l int

       set @s = replace(@s, '0x','')
       select @l = len(@s)
              ,@n = 1

       while @n <= @l-1
       begin
	       select	@out = coalesce(@out + b, b)
              from   (
                     select b = convert(binary(1),
			       (convert(varbinary, charindex(substring(@s, @n, 1), '0123456789ABCDEF')-1) * 16)
			       + isNull(nullif(charindex(substring(@s, @n+1, 1), '0123456789ABCDEF')-1,-1),0))
                     ) d
              set @n = @n+2
       end

       return @out
end
go

It assumes the input comes in Pairs of hex digits (one byte each). So it looks like your sample value is missing a leading "0":
'0A5000000020'. So if your data has odd numbers of digits you may have an issue.

Then you can use it with something like this:


select convert(bigint, dbo.fn_hexStrToVarbin(a.Client_id)) 
from [application] as a 
where a.application_id = @application_id

Be One with the Optimizer
TG

KRichradson1127
Starting Member

20 Posts

Posted - 2009-07-31 : 11:28:29

Ok TG...one more thing

insert into dbo.application
(Company_ID_MySQL)
select convert(bigint, dbo.fn_hexStrToVarbin(a.new_client_id))
from [application] a
where a.application_id = a.application_id

Whenever I try to insert data into this row I get this error message:
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'client_id', table 'AccreditNet_Copied.dbo.application'; column does not allow nulls. INSERT fails.

I am not inserting anything into the client_id column...why does this error message appear?

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-07-31 : 13:15:52

Your client_id column is defined as a non-nullable column, meaning it cannot accept NULL values. When you don't specify the values to be inserted into it, the database engine will attempt to insert NULLs into it, hence giving you your error. You need to specify the client_id when inserting into that table.

Some days you're the dog, and some days you're the fire hydrant.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources