Solved

How to convert a varbinary to string in SQL

Posted on 2006-11-17
6
5,136 Views
Last Modified: 2008-01-09
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
Comment
Question by:novice12
  • 4
  • 2
6 Comments
 
LVL 29

Expert Comment

by:Nightman
ID: 17969258
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
 

Author Comment

by:novice12
ID: 17969286
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
 
LVL 29

Accepted Solution

by:
Nightman earned 460 total points
ID: 17969301
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:novice12
ID: 17969335
Your answer works. The function does work in SQL 7. Thanks
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17969361
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
 
LVL 29

Expert Comment

by:Nightman
ID: 17969480
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question