How to convert a varbinary to string in SQL

I have a table with a column called "hexcode"
The data type in this column is varbinary with a length of 12, and looks like this:
0x1A000000FCFF12000200371D8041591B403C381D8041591B403C

I need to do the following:
I need to extract a substring from the above data to end up with only:
371D8041591B403C381D8041591B403C

so basically I would like to get rid off the leading: 0x1A000000FCFF12000200

How do I do this?

novice12Asked:
Who is Participating?
 
NightmanConnect With a Mentor CTOCommented:
OK, functions didn't exists in SQL 7 (bear with me, it's been a while since I worked with anything below SQL 2000).
0
 
NightmanCTOCommented:
SELECT SUBSTRING(master.dbo.fn_varbintohexstr(YourColumn),23,32)

Although the fact that it is varbinary 12 may cause problems - at a glance I would guess that this should be varbinary(26)
0
 
novice12Author Commented:
This is Microsoft Transact SQL. The data base is SQL 7.
The table is in a user database call it "MyDatabase" and not in the master database.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
novice12Author Commented:
Your answer works. The function does work in SQL 7. Thanks
0
 
NightmanCTOCommented:
Cool - I was busy trying to reverse engineer the source code of the function to be a stored proc ;)

I forgot, it's UDF's that aren't in SQL 7.

Glad to help
0
 
NightmanCTOCommented:
Ported it to a stored procedure anyway - I guess I'm a bit of a geek ;)


CREATE procedure varbintohexsubstring (
    @pbinin varbinary(8000), -- input binary stream
    @pstrout nvarchar(4000) output
)
as
  declare @fsetprefix bit
  declare @startoffset int
  declare @cbytesin int
    declare @i int
    declare @firstnibble int
    declare @secondnibble int
    declare @tempint int
    declare @hexstring char(16)


    --
    -- initialize and validate
    --
    if (@pbinin IS NOT NULL)
    begin    
    select @fsetprefix=1
    select @startoffset=1
    select @cbytesin=0
        select @i = 0
                ,@cbytesin = case when (@cbytesin > 0) then @cbytesin else DATALENGTH(@pbinin) end
                ,@pstrout =  case when (@fsetprefix = 1) then N'0x' else N'' end
                ,@hexstring = '0123456789abcdef'
        if ( ((@cbytesin * 2) + 2 > 4000) or ((@cbytesin * 2) + 2 < 1) )
      begin
            select @pstrout=NULL
      return
    end
        if ( ( @startoffset > @cbytesin ) or ( @startoffset < 1 ) )
        begin
        select @pstrout=NULL
      return
    end            
        --
        -- do for each byte
        --
        while (@i < @cbytesin)
        begin
            --
            -- Each byte has two nibbles
            -- which we convert to character
            --
            select @tempint = cast(substring(@pbinin, @i + @startoffset, 1) as int)
            select @firstnibble = @tempint / 16
            select @secondnibble = @tempint % 16

            --
            -- we need to do an explicit cast with substring
            -- for proper string conversion.
            --
            select @pstrout = @pstrout +
                cast(substring(@hexstring, (@firstnibble+1), 1) as nvarchar) +
                cast(substring(@hexstring, (@secondnibble+1), 1) as nvarchar)

            select @i = @i + 1
        end
    end

    -- All done
    return
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.