Converting Hex String to Int

Question

Post reply

Converting Hex String to Int

AK007

SSC Veteran

Points: 213
More actions
September 1, 2009 at 3:38 am

#210153

When converting a constant hex string '09FF000' to INT. It is simple as below
Select Convert(Int, 0x09FF000) --just added 0x
However when hex string is in a table and stored as varchar. So I tried following
Declare @hex varchar(50)
Select @hex = '0x' + '09FF000' --Retrieve hex string from a table
Select Convert(Int, @hex)
Above statement throws an error. I also tried to first convert hex string to varbinary, that also throws an error.
Is there a built in function is SQL 2005?
Any other idea or resolution, please suggest???????

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

eharper SSC Eights! Points: 802 More actions · Answer 1

There is no built in function - you would need to create your own.

See http://blogs.msdn.com/rextang/archive/2008/01/13/7091118.aspx

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 2

Try this link

http://michaeldotnet.blogspot.com/2007/11/t-sql-hex-string-to-varbinary-improved.html

Clear Sky SQL
My Blog[/url]

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

Here is a script posted to SSC by Ofer Bester - all you need do, is a bit of copy and paste and you have what you want

http://www.sqlservercentral.com/scripts/Miscellaneous/30094/

I have used it and it works very well

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

pmcpherson SSC-Addicted Points: 476 More actions · Answer 4

Because there is a general revulsion for row by agonizing row,

a person could use a tally table and a values table rather than a while loop to accomplish the same task.

Here is some code you can copy and paste to test with.

You should probably make these tables real tables not temp tables.

The first one is the reference for the value of each valid hex character.

CREATE TABLE #HexConvertTbl (Val TINYINT, Hex CHAR(1) PRIMARY KEY )

INSERT #HexConvertTbl ( Val, Hex )

SELECT 0, '0'

UNION SELECT 1, '1'

UNION SELECT 2, '2'

UNION SELECT 3, '3'

UNION SELECT 4, '4'

UNION SELECT 5, '5'

UNION SELECT 6, '6'

UNION SELECT 7, '7'

UNION SELECT 8, '8'

UNION SELECT 9, '9'

UNION SELECT 10, 'A'

UNION SELECT 11, 'B'

UNION SELECT 12, 'C'

UNION SELECT 13, 'D'

UNION SELECT 14, 'E'

UNION SELECT 15, 'F'

--I hope you already have a Tally Table, just in case, here is a short one.

--The tally table is for position counting

CREATE TABLE #TallyTBL (N INT PRIMARY KEY)

INSERT #TallyTBL ( N )

SELECT 1

UNION SELECT 2

UNION SELECT 3

UNION SELECT 4

UNION SELECT 5

UNION SELECT 6

UNION SELECT 7

UNION SELECT 8

UNION SELECT 9

UNION SELECT 10

UNION SELECT 11

UNION SELECT 12

UNION SELECT 13

UNION SELECT 14

UNION SELECT 15

UNION SELECT 16

UNION SELECT 17

UNION SELECT 18

UNION SELECT 19

UNION SELECT 20

--Ideally these would be parameters of a function

DECLARE @Hex varchar(16), @Len TINYINT

--Sample data, hopefully this gets passed in as a parameter

--You might have to clean up 0xFFFFFF format, but that should be easy enough

SELECT @Hex = 'F270AF'

--Limit for the number of rows the tally table split allows

SELECT @Len = LEN(@Hex)

--Summarize the values of each column

SELECT SUM(Q1.ColumnVal) as DecimalVal

FROM (

--This is where the real work is done. For column values discussion see: http://www.codemastershawn.com/library/tutorial/hex.bin.numbers.php

--For Tally table split reference, see: http://www.sqlservercentral.com/articles/T-SQL/62867/

SELECT SUBSTRING(@hex,N,1) Hex, h.Val, POWER(16,@Len-N)*h.Val AS ColumnVal

FROM #TallyTBL t

JOIN #HexConvertTbl h

ON h.Hex = SUBSTRING(@hex,N,1)

WHERE N <= @Len

) AS Q1

DROP TABLE #HexConvertTbl

DROP TABLE #TallyTBL

Does someone know how to use a Tally Table method to account for signed hex values?

Our process is in place for values that will always be greater than 0.

Ronzo Hall of Fame Points: 3917 More actions · Answer 5

I adapted the following from this link (https://social.msdn.microsoft.com/Forums/en-US/20e92e10-a0ab-4a53-a766-76f84bfd4e8c/converting-hex-values-to-int?forum=transactsql).

It makes use of a "dummy" XML CAST so as to be able to use ".value".

I CAST the resulting hex from the string column value to INT since I needed to do this, but you can easily remove that part if desired.

(substitute your column name for col1, and of course supply your table/view after the final FROM):

SELECT

CAST

(

SELECT

CAST ('' AS XML).value('xs:hexBinary(substring(sql:column("col1"), sql:column("t.pos")) )', 'varbinary(max)')

FROM

(

SELECT

CASE SUBSTRING (col1, 1, 2)

WHEN '0x'

THEN 3

ELSE 0

END

)

AS t(pos)

)

AS INT

)

FROM

Have Fun!
Ronzo

Sergiy SSC Guru Points: 110208 More actions · Answer 6

Check if you can find this function in master database on SQL2005.

But this works on SQL 2008:

Declare @hex VARCHAR(50)

Select @hex = '0x' + '009FF000' -- I added missing zero in front of the string

SELECT Convert(Int, 0x09FF000) , CONVERT(Int, substring([sys].[fn_cdc_hexstrtobin](@hex), 1,4)), [sys].[fn_cdc_hexstrtobin](@hex)

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 7

For the sake of fun:

You might wish to convert to other integer data types, not only int.

Try to use different types for @tgt in this script:

Declare @hex VARCHAR(50), @tgt TINYINT, @typeLength TINYINT

SET @tgt = 0

SELECT @typeLength = DATALENGTH(@tgt)

Select @hex = 'ff' --9FF000' --Retrieve hex string from a table

Select @hex = '0x' + REPLICATE('0', @typeLength*2-LEN(LTRIM(@hex))) + LTRIM(@hex) --Retrieve hex string from a table

set @tgt = substring([sys].[fn_cdc_hexstrtobin](@hex), 1, @typeLength)

SELECT @tgt, [sys].[fn_cdc_hexstrtobin](@hex)

_____________
Code for TallyGenerator