24

This answer to what looks like the same question:

Convert integer to hex and hex to integer

..does not work for me.

I am not able to go to a HEX string to an integer using MS SQL server 2005 CAST or CONVERT. Am I missing something trivial? I have searched extensively, and the best I can find are long-winded user functions to go from a hex string value to something that looks like a decimal int. Surely there is a simple way to do this directly in a query using built in functions rather than writing a user function?

Thanks

Edit to include examples:

select CONVERT(INT, 0x89)

works as expected, but

select CONVERT(INT, '0x' + substring(msg, 66, 2)) from sometable

gets me:

"Conversion failed when converting the varchar value '0x89' to data type int."

an extra explicit CAST:

select CONVERT(INT, CAST('0x89' AS VARBINARY))

executes, but returns 813185081.

Substituting 'Int', 'Decimal', etc for 'Varbinary' results in an error. In general, strings that appear to be numeric are interpreted as numeric if required, but not in this case, and there does not appear to be a CAST that recognizes HEX. I would like to think there is something simple and obvious and I've just missed it.

Microsoft SQL Server Management Studio Express 9.00.3042.00

Microsoft SQL Server 2005 - 9.00.3080.00 (Intel X86) Sep 6 2009 01:43:32 Copyright (c) 1988-2005 Microsoft Corporation Express Edition with Advanced Services on Windows NT 5.1 (Build 2600: Service Pack 3)

To sum up: I want to take a hex string which is a value in a table, and display it as part of a query result as a decimal integer, using only system defined functions, not a UDF.

Improve this question
6
  • 1
    Are you getting a specific error, or unexpected results? Examples would be helpful. Commented Aug 30, 2010 at 22:19
  • Have you seen this? Look at the comments... blog.sqlauthority.com/2010/02/01/… Commented Aug 30, 2010 at 22:34
  • This is one of the first posts that came up when I searched. Every single one of these answers either uses a user function, a DLL (for cryin' out loud!) or suggests the built in CONVERT which so far I am not able to use. The suggestion to use it appears to have been copied many times by many people who did not test it themselves. Commented Aug 30, 2010 at 23:25
  • I think you're going to have to use a UDF for that: digitalcolony.com/2007/06/… Commented Aug 31, 2010 at 0:14
  • What does "does not work mean"? CONVERT() is a built-in function and it's extremely unlikely that it doesn't work. So please post your MSSQL version and edition, plus the results of running the two SELECT queries from the answer you linked to: SELECT CONVERT(VARBINARY(8), 16777215) and SELECT CONVERT(INT, 0xFFFFFF) Commented Aug 31, 2010 at 7:46

2 Answers 2

Reset to default
40

Thanks for giving some more explicit examples. As far as I can tell from the documentation and Googling, this is not possible in MSSQL 2005 without a UDF or other procedural code. In MSSQL 2008 the CONVERT() function's style parameter now supoprts binary data, so you can do it directly like this:

select convert(int, convert(varbinary, '0x89', 1))

In previous versions, your choices are:

  • Use a UDF (TSQL or CLR; CLR might actually be easier for this)
  • Wrap the SELECT in a stored procedure (but you'll probably still have the equivalent of a UDF in it anyway)
  • Convert it in the application front end
  • Upgrade to MSSQL 2008

If converting the data is only for display purposes, the application might be the easiest solution: data formatting usually belongs there anyway. If you must do it in a query, then a UDF is easiest but the performance may not be great (I know you said you preferred not to use a UDF but it's not clear why). I'm guessing that upgrading to MSSQL 2008 just for this probably isn't realistic.

Finally, FYI the version number you included is the version of Management Studio, not the version number of your server. To get that, query the server itself with select @@version or select serverproperty('ProductVersion').

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks, and thanks for making me clarify my question. I looked for Style parameters for the version of Convert I have, thinking it might be there, but came up short. This was an ad-hoc query (no app.) and I hoped to avoid tweaking the DB by adding a UDF. At some point it may become part of an app and all these issues will go away.
Interesting to note that this does not work: select convert(int, convert(varbinary, '0x1', 1)) But this DOES: select convert(int, convert(varbinary, '0x01', 1)) For some reason the leading zero AFTER the 0x matters for single digit strings.
@carl.anderson This behaviour is explicitly documented: "The expression must be composed of an even number of hexadecimal digits... If the expression contains an odd number of characters... an error is raised."
also works for me for getting unicode text characters...... big help cleaning ascii string data with "\uNNNN" rather than proper unicode text... i.e.---select nchar(convert(int, convert(varbinary, '0x56fd', 1)))
0

I use the following Scalar Valued Function (on more recent servers than the original question had targeted), incorporating the answers/hints of @pondlife and @carl.anderson and solving a few other issues. Returns either the according INT value or NULL.

  • SQLServer2022 (Compatibility Level 160):

    CREATE FUNCTION [dbo].[HexStringToInt32](@HexString VARCHAR(MAX)) 
RETURNS INT WITH SCHEMABINDING
AS
BEGIN
    IF (@HexString IS NULL) OR (@HexString = '') RETURN NULL;
    SET @HexString = LTRIM(RTRIM(@HexString));
    IF (@HexString = '') RETURN NULL;
    SET @HexString = LTRIM(@HexString, '0');
    IF (@HexString = '') RETURN 0;
    IF (LEN(@HexString) > 8) RETURN NULL;
    DECLARE @HasOddLength AS BIT = LEN(@HexString) % 2;
    DECLARE @HexStringInclPrefix VARCHAR(10) = CASE WHEN @HasOddLength = 1 THEN '0x0' ELSE '0x' END + @HexString;
    RETURN TRY_CONVERT(INT, TRY_CONVERT(VARBINARY, @HexStringInclPrefix, 1))
END

  • Pre-SQLServer2022 (Compatibility Level <= 150):

     CREATE FUNCTION [dbo].[HexStringToInt32](@HexString VARCHAR(MAX)) 
 RETURNS INT WITH SCHEMABINDING
 AS
 BEGIN
     IF (@HexString IS NULL) OR (@HexString = '') RETURN NULL;
     SET @HexString = LTRIM(RTRIM(@HexString));
     IF (@HexString = '') RETURN NULL;
     WHILE @HexString LIKE '0_%' BEGIN
         SET @HexString = SUBSTRING(@HexString, 2, LEN(@HexString) - 1);
     END
     IF (LEN(@HexString) > 8) RETURN NULL;
     DECLARE @HasOddLength AS BIT = LEN(@HexString) % 2;
     DECLARE @HexStringInclPrefix VARCHAR(10) = CASE WHEN @HasOddLength = 1 THEN '0x0' ELSE '0x' END + @HexString;
     RETURN TRY_CONVERT(INT, TRY_CONVERT(VARBINARY, @HexStringInclPrefix, 1))
 END

  • Usage/Tests:

     SELECT dbo.HexStringToInt32(NULL) --> NULL (NULL returns NULL)
 SELECT dbo.HexStringToInt32('') --> NULL (an empty string returns NULL)
 SELECT dbo.HexStringToInt32('F') --> 15 (an odd number of hex digits is handled correctly)
 SELECT dbo.HexStringToInt32('ABBA') --> 43962 (an even number of hex digits is handled correctly)
 SELECT dbo.HexStringToInt32('BEATLES') --> NULL (an invalid hex string returns NULL)
 SELECT dbo.HexStringToInt32('12345678') --> 305419896 (8 hex digits are max)
 SELECT dbo.HexStringToInt32('FFFFFFFF') --> -1 (negative values are supported)
 SELECT dbo.HexStringToInt32('123456789') --> NULL (9 hex digits return NULL)
 SELECT dbo.HexStringToInt32('12345678 1') --> NULL (10 characters with a space at position 9 returns NULL - I was tempted to use a VARCHAR(9) instead of a VARCHAR(MAX), but unfortunately does not work)
 SELECT dbo.HexStringToInt32('    000012345678   ') --> 305419896 (A length of more than 8 characters is tolerated if the additional characters are leading and/or trailing space and/or leading zeros)
 SELECT dbo.HexStringToInt32('0000    12345678    ') --> NULL (A length of more than 8 characters is not tolerated if the spaces come after the leading zeros)
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.