Solved

How to convert a varbinary to string in SQL

Posted on 2006-11-17
6
5,130 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Insert to Begin if data exists 2 31
Row insertion failed. Array 5 46
sql, case when & top 1 14 26
question about results where i dont have a match 3 20
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

809 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