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') .

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)