Solved

How to convert a varbinary to string in SQL

Posted on 2006-11-17
6
5,148 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

628 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