Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Converting Hex String to Int Expand / Collapse
Author
Message
Posted Tuesday, September 1, 2009 3:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 21, 2011 6:07 AM
Points: 3, Visits: 90
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???????
Post #780531
Posted Tuesday, September 1, 2009 3:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 3:30 AM
Points: 30, Visits: 533
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
Post #780542
Posted Tuesday, September 1, 2009 4:00 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:06 AM
Points: 1,768, Visits: 8,318
Try this link

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




Clear Sky SQL
My Blog
Kent user group
Post #780543
Posted Tuesday, September 1, 2009 9:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
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

Before posting a performance problem please read
Post #780822
Posted Wednesday, March 13, 2013 12:10 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, November 20, 2014 10:41 PM
Points: 56, Visits: 412
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.
Post #1430562
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse