Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5173
  • Last Modified:

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?

0
novice12
Asked:
novice12
  • 4
  • 2
1 Solution
 
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
 
NightmanCTOCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now