July 17, 2018 at 11:30 pm
How can convert hex value to decimal in sql server 2012
July 18, 2018 at 2:29 am
You can use CONVERT function like this:
SELECT CONVERT(DECIMAL, CONVERT(INT, 0x0010)) // This will return 16
July 18, 2018 at 6:41 am
debasis.yours - Wednesday, July 18, 2018 2:29 AMYou can use CONVERT function like this:
SELECT CONVERT(DECIMAL, CONVERT(INT, 0x0010)) // This will return 16
this solutions not working for below values:
SELECT CONVERT(DECIMAL, CONVERT(VARBINARY(3000), '143307ac2b0a93795113'))
Error:
Msg 8115, Level 16, State 6, Line 1
Arithmetic overflow error converting varbinary to data type numeric.
July 18, 2018 at 6:46 am
That doesn't seem to be a HEX value. Is it?
July 18, 2018 at 9:54 am
kailashIndia - Tuesday, July 17, 2018 11:30 PMHow can convert hex value to decimal in sql server 2012
kailashIndia - Wednesday, July 18, 2018 6:41 AMdebasis.yours - Wednesday, July 18, 2018 2:29 AMYou can use CONVERT function like this:
SELECT CONVERT(DECIMAL, CONVERT(INT, 0x0010)) // This will return 16this solutions not working for below values:
SELECT CONVERT(DECIMAL, CONVERT(VARBINARY(3000), '143307ac2b0a93795113'))Error:
Msg 8115, Level 16, State 6, Line 1
Arithmetic overflow error converting varbinary to data type numeric.
That value is too large by 4 hex digits for any attempt to convert to bigint, and also too large for any direct conversion to decimal. You'll have to seek out some kind of T-SQL function (possibly CLR). I don't have time to code one up at the moment, but perhaps someone else can give it a shot.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 18, 2018 at 10:43 am
sgmunson - Wednesday, July 18, 2018 9:54 AMkailashIndia - Tuesday, July 17, 2018 11:30 PMHow can convert hex value to decimal in sql server 2012kailashIndia - Wednesday, July 18, 2018 6:41 AMdebasis.yours - Wednesday, July 18, 2018 2:29 AMYou can use CONVERT function like this:
SELECT CONVERT(DECIMAL, CONVERT(INT, 0x0010)) // This will return 16this solutions not working for below values:
SELECT CONVERT(DECIMAL, CONVERT(VARBINARY(3000), '143307ac2b0a93795113'))Error:
Msg 8115, Level 16, State 6, Line 1
Arithmetic overflow error converting varbinary to data type numeric.That value is too large by 4 hex digits for any attempt to convert to bigint, and also too large for any direct conversion to decimal. You'll have to seek out some kind of T-SQL function (possibly CLR). I don't have time to code one up at the moment, but perhaps someone else can give it a shot.
There is a CLR example posted in the comment by Marko Parkkola for an article on this same topic:
How to Convert Hex to Decimal
Sue
July 19, 2018 at 9:50 am
Took a few minutes today and coded something that will work on the string representation of the hex value. Hope it comes in handy. It's an ITVF, or inline-table-valued function. Please feel free to improve it and/or provide feedback.CREATE FUNCTION dbo.HexStringToDecimal (
@VarBinChar varchar(22)
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
WITH XREF AS (
SELECT '0' AS Chr, 0 AS Num UNION ALL
SELECT '1', 1 UNION ALL
SELECT '2', 2 UNION ALL
SELECT '3', 3 UNION ALL
SELECT '4', 4 UNION ALL
SELECT '5', 5 UNION ALL
SELECT '6', 6 UNION ALL
SELECT '7', 7 UNION ALL
SELECT '8', 8 UNION ALL
SELECT '9', 9 UNION ALL
SELECT 'A', 10 UNION ALL
SELECT 'B', 11 UNION ALL
SELECT 'C', 12 UNION ALL
SELECT 'D', 13 UNION ALL
SELECT 'E', 14 UNION ALL
SELECT 'F', 15
),
Numbers AS (
SELECT 1 AS N UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1
),
Tally AS (
SELECT TOP (LEN(@VarBinChar)) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS CharPos
FROM Numbers AS N1
CROSS APPLY Numbers AS N2
)
SELECT
@VarbinChar AS VarBinChar,
SUM(Y.NumValue) AS DecimalValue,
FORMAT(SUM(Y.NumValue), '##,#', 'en-US') AS StringRepresentation
FROM (
SELECT TOP 100 PERCENT T.CharPos, C.TheChar, C.Multiplier, C.NumValue
FROM Tally AS T
CROSS APPLY (
SELECT
SUBSTRING(REVERSE(@VarBinChar), T.CharPos, 1) AS TheChar,
POWER(CONVERT(decimal(25,0), 16), (T.CharPos - 1)) AS Multiplier,
POWER(CONVERT(decimal(25,0), 16), (T.CharPos - 1)) * X.Num AS NumValue
FROM XREF AS X
WHERE X.Chr = SUBSTRING(REVERSE(@VarBinChar), T.CharPos, 1)
) AS C
ORDER BY T.CharPos ASC
) AS Y;
GO
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 15, 2023 at 6:38 pm
Nicely done, Mr. Munson.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 15, 2023 at 8:47 pm
Post deleted... we may have found an even better way yet. I'll be back.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 16, 2023 at 5:22 am
This was removed by the editor as SPAM
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply