T-SQL Convert Hex to decimal

Question

I try to use TSQL and I get some problem to convert hex to decimal when the value is inside a variable.

My code is:

SET @hex = SUBSTRING(@x,1,2) --give 1e
SET @hex = '0x' + @hex
SELECT CAST (@hex AS int)

I get the error that the conversion failed when converting the varchar value to int

I don't know what it's wrong and so far I can't find solution

Any idea? Thanks

Answer 1

I don't think T-SQL can explicitly convert a string of hex to a number, but it CAN convert a hex number so you can kind of trick it into doing what you want. Like this:

DECLARE @hex nvarchar(20)
DECLARE @select nvarchar(200)
DECLARE @parm nvarchar(200)
DECLARE @retVal int
SET @hex = '0x1e'
SET @select = 'SELECT @retVal = CAST(' + @hex + ' AS int)'
SET @parm = '@retVal int OUTPUT'
EXEC sp_executesql @select, @parm, @retVal OUTPUT
SELECT @retVal

You'll want to validate @hex to protect from SQL injection attacks, and you'll probably want to put this in a function or stored proc

Answer 2

CREATE FUNCTION dbo.FN_HEXTOINT(@HEX varchar(MAX) = NULL)
RETURNS @RETORNO TABLE(INTHEX INT)
AS 
BEGIN
  
  SET @HEX = UPPER(@HEX)
  
  DECLARE @TABHEX VARCHAR(16) = '0123456789ABCDEF'
  DECLARE @CHARX VARCHAR(1)
  DECLARE @POSCHARX INT 
  DECLARE @BASE INT = 16
  DECLARE @I INT = LEN(@HEX)
  DECLARE @LENSTR INT = LEN(@HEX)
  DECLARE @EXPOENTE INT = 36
  DECLARE @N10 FLOAT = 0.01

  
    WHILE (@I> 0)
    BEGIN
    
     SET @CHARX = SUBSTRING (@HEX, @i,1)
     SET @POSCHARX  = PATINDEX('%'+@CHARX+'%',  @TABHEX);  
     IF @POSCHARX > 0 
     BEGIN
       SET @N10 = @N10 + ((@POSCHARX - 1) * POWER(@BASE, (@LENSTR-@I)))  
     END
       
    SET @I = @I - 1
    
    END
    
      INSERT INTO @RETORNO 
    SELECT (ROUND(@N10 + 0.001,1)) AS RET
  
RETURN 
END