## Converting Hex String to Int

 Author Message AK007 Grasshopper Group: General Forum Members Points: 23 Visits: 90 When converting a constant hex string '09FF000' to INT. It is simple as belowSelect Convert(Int, 0x09FF000) --just added 0xHowever when hex string is in a table and stored as varchar. So I tried followingDeclare @hex varchar(50)Select @hex = '0x' + '09FF000' --Retrieve hex string from a tableSelect 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??????? eharper SSC Veteran Group: General Forum Members Points: 224 Visits: 549 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 SSCertifiable Group: General Forum Members Points: 7366 Visits: 8370 bitbucket-25253 SSCoach Group: General Forum Members Points: 16199 Visits: 25280 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 wanthttp://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. RonPlease help us, help you -before posting a question please read Before posting a performance problem please read pmcpherson SSC-Enthusiastic Group: General Forum Members Points: 155 Visits: 476 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 countingCREATE TABLE #TallyTBL (N INT PRIMARY KEY) INSERT #TallyTBL ( N )SELECT 1UNION SELECT 2UNION SELECT 3UNION SELECT 4UNION SELECT 5UNION SELECT 6UNION SELECT 7UNION SELECT 8UNION SELECT 9UNION SELECT 10UNION SELECT 11UNION SELECT 12UNION SELECT 13UNION SELECT 14UNION SELECT 15UNION SELECT 16UNION SELECT 17UNION SELECT 18UNION SELECT 19UNION SELECT 20--Ideally these would be parameters of a functionDECLARE @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 enoughSELECT @Hex = 'F270AF'--Limit for the number of rows the tally table split allowsSELECT @Len = LEN(@Hex)--Summarize the values of each columnSELECT SUM(Q1.ColumnVal) as DecimalValFROM ( --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 Q1DROP TABLE #HexConvertTblDROP 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. Ron Clarke Ten Centuries Group: General Forum Members Points: 1189 Visits: 829 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 One Orange Chip Group: General Forum Members Points: 26272 Visits: 12487 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 stringSELECT Convert(Int, 0x09FF000) , CONVERT(Int, substring([sys].[fn_cdc_hexstrtobin](@hex), 1,4)), [sys].[fn_cdc_hexstrtobin](@hex)` Sergiy One Orange Chip Group: General Forum Members Points: 26272 Visits: 12487 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 = 0SELECT @typeLength = DATALENGTH(@tgt)Select @hex = 'ff' --9FF000' --Retrieve hex string from a tableSelect @hex = '0x' + REPLICATE('0', @typeLength*2-LEN(LTRIM(@hex))) + LTRIM(@hex) --Retrieve hex string from a tableset @tgt = substring([sys].[fn_cdc_hexstrtobin](@hex), 1, @typeLength)SELECT @tgt, [sys].[fn_cdc_hexstrtobin](@hex)`