Share via

T-SQL: Convert Hex String to VarBinary...

  • Article

Here is some conversion functions in SQL Server...

To convert between int and hex values, by referring to this post:

SELECT 'Int -> Hex'
SELECT CONVERT(VARBINARY(8), 16777215)
SELECT 'Hex -> Int'
SELECT CONVERT(INT, 0xFFFFFF)

To convert a VarBinary to a HexString, by referring to Peter DeBetta's post, there is a function in SQL Server 2005 with the name "sys.fn_varbintohexstr" to do the job.

To convert a HexString to a VarBinary, there is no built-in function to do so. Peter DeBetta wrote a function to do this in above post link, and later at Michael.Net's blog post, there is an improved function to do the job. I'll also document here for my reference.

 CREATE FUNCTION dbo.HexStrToVarBinary(@hexstr varchar(8000))
RETURNS varbinary(8000)
AS
BEGIN 
    DECLARE @hex char(1), @i int, @place bigint, @a bigint
    SET @i = LEN(@hexstr) 

    set @place = convert(bigint,1)
    SET @a = convert(bigint, 0)

    WHILE (@i > 0 AND (substring(@hexstr, @i, 1) like '[0-9A-Fa-f]')) 
     BEGIN 
        SET @hex = SUBSTRING(@hexstr, @i, 1) 
        SET @a = @a + 
    convert(bigint, CASE WHEN @hex LIKE '[0-9]' 
         THEN CAST(@hex as int) 
         ELSE CAST(ASCII(UPPER(@hex))-55 as int) end * @place)
    set @place = @place * convert(bigint,16)
        SET @i = @i - 1
    
     END 

    RETURN convert(varbinary(8000),@a)
END
GO

I'll use those functions in my next post to check the session size in asp.net applications...

Technorati Tags: microsoft,sqlserver,tsql,t-sql,programming,conversion

Comments

  • Anonymous
    January 12, 2008
    PingBack from http://geeklectures.info/2008/01/12/t-sql-convert-hex-string-to-varbinary/

  • Anonymous
    January 12, 2008
    by setting asp.net application to save session state into SQL Server database, not only can scale application

  • Anonymous
    January 12, 2008
    by setting asp.net application to save session state into SQL Server database, not only can scale application

  • Anonymous
    January 12, 2008
    PingBack from http://msdnrss.thecoderblogs.com/2008/01/12/aspnet-checking-session-size-in-sql-server-aspstate-db/

  • Anonymous
    June 01, 2009
    PingBack from http://paidsurveyshub.info/story.php?id=75011

  • Anonymous
    June 15, 2009
    PingBack from http://edebtsettlementprogram.info/story.php?id=22719

  • Anonymous
    August 06, 2009
    --Edit by Ali Motamed Rezaie (motamedrezaie@gmail.com) CREATE FUNCTION dbo.HexStrToVarBinary(    @hexstr VARCHAR(8000) )RETURNS VARBINARY(8000) AS BEGIN    DECLARE @ret VARBINARY(8000);    DECLARE @chr1 CHAR(1);    DECLARE @chr2 CHAR(1);    DECLARE @i INT;    DECLARE @len INT;    SET @len = LEN(@hexstr);    SET @i = 1;    SET @ret = CAST('' AS VARBINARY);    SET @chr1 = SUBSTRING(@hexstr, @i, 1);    SET @chr2 = SUBSTRING(@hexstr, @i + 1, 1);    WHILE (@i <= @len AND @chr1 LIKE '[0-9A-Fa-f]' AND @chr2 LIKE '[0-9A-Fa-f]')    BEGIN        SET @chr1 = SUBSTRING(@hexstr, @i, 1);        SET @chr2 = SUBSTRING(@hexstr, @i + 1, 1);        SET @ret = @ret + CAST(((CASE WHEN @chr1 LIKE '[0-9]' THEN CAST(@chr1 AS TINYINT)                            ELSE CAST(ASCII(UPPER(@chr1)) - 55 AS TINYINT)                            END * CAST(16 AS TINYINT))                            + CASE WHEN @chr2 LIKE '[0-9]' THEN CAST(@chr2 AS TINYINT)                            ELSE CAST(ASCII(UPPER(@chr2)) - 55 AS TINYINT)                            END)                                                        AS VARBINARY);        SET @i = @i + 2;    END    RETURN @ret; END