SQL Server Performance

Converting varbinary to char or varchar

Discussion in 'General Developer Questions' started by rushmada, Jun 3, 2003.

  1. rushmada New Member

    How to convert a varbinary column into a char or varchar column.

    Rushendra
  2. vbkenya New Member

    If you just want to have the varbinary data represented as varchar or char without further processing (e.g. getting the actual meaning of the varbinary hexadecimal stuff) you have 2 system functions you can use.

    (1)fn_varbintohexstr
    (2)fn_varbintohexsubstring

    Both are in the master database and they return 'nvarchar' values.


    NHO
  3. rushmada New Member

    Thanks for ur info. I could'nt see this functions in SQL 7. Is there any alternative.


    quote:Originally posted by vbkenya

    If you just want to have the varbinary data represented as varchar or char without further processing (e.g. getting the actual meaning of the varbinary hexadecimal stuff) you have 2 system functions you can use.

    (1)fn_varbintohexstr
    (2)fn_varbintohexsubstring

    Both are in the master database and they return 'nvarchar' values.


    NHO

    Rushendra
  4. bambola New Member

    You can write your own stored procedure using the code in fn_varbintohexsubstring(). There's doesn't seem to be anything particular to sql 2000 so it should work. If you need the source code let me know.

    Bambola.
  5. rushmada New Member

    If u give the code that will be helpfull for me

    Thanks


    quote:Originally posted by bambola

    You can write your own stored procedure using the code in fn_varbintohexsubstring(). There's doesn't seem to be anything particular to sql 2000 so it should work. If you need the source code let me know.

    Bambola.

    Rushendra
  6. bambola New Member


    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS OFF
    GO


    --
    -- System UDF fn_varbintohexsubstring
    -- This function takes as input a varbinary, and start offset and length
    --and returns the Hexadecimal representation string for the given substring
    --
    create function dbo.fn_varbintohexsubstring (
    @fsetprefix bit = 1-- append '0x' to the output
    ,@pbinin varbinary(8000) -- input binary stream
    ,@startoffset int = 1 -- starting offset
    ,@cbytesin int = 0 -- length of input to consider, 0 means total length
    )
    returns nvarchar(4000)
    as
    begin
    declare @pstrout nvarchar(4000)
    ,@i int
    ,@firstnibble int
    ,@secondnibble int
    ,@tempint int
    ,@hexstring char(16)

    --
    -- initialize and validate
    --
    if (@pbinin IS NOT NULL)
    begin
    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) )
    return NULL

    if ( ( @startoffset > @cbytesin ) or ( @startoffset < 1 ) )
    return NULL

    --
    -- 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 @pstrout
    end

    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS OFF
    GO


    --
    -- System UDF fn_varbintohexstr
    -- This function takes as input a varbinary
    --and returns the Hexadecimal representation string
    --
    create function dbo.fn_varbintohexstr (
    @pbinin varbinary(8000) )
    returns nvarchar(4000)
    as
    begin
    declare @pstrout nvarchar(4000)

    select @pstrout = master.dbo.fn_varbintohexsubstring(1,@pbinin,1,0)
    return @pstrout
    end

    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    Bambola.

  7. icata New Member

    Hi,

    How can I get the meaning of a varbinary hexadecimal?

    Ionel
  8. rushmada New Member

    Binary data consists of hexadecimal numbers. For example, the decimal number 245 is hexadecimal F5. Binary data is stored using the binary, varbinary SQL Server. A column that is assigned the binary data type must have the same fixed length (up to 8 KB) for each row. In a column that is assigned the varbinary data type, entries can vary in the number of hexadecimal digits (up to 8 KB) they contain.

    For more info Pl see topic data types in sql server books online.

    Thanks.



    quote:Originally posted by icata

    Hi,

    How can I get the meaning of a varbinary hexadecimal?

    Ionel

    Rushendra
  9. vbkenya New Member

    Just converting the hexadecimal value to a 'legible' type(varchar,integer etc) may not unravel the actual 'meaning' of data. The meaning will depend on exactly why it is saved in the database in the first place.

    Before trying to work out the meaning of most hexadecimal representations of data, remember that SQL Server will more often than not save the data in reverse hexadecimal order. You will encounter this in the transaction log records if you have tried analyzing them.

    To keep it simple:
    -Convert every 2 hexadecimal digits in your data to an integer value (two hexadecimal characters represent 1 byte of varchar data)
    -Convert the resulting integer values to their ASCII character equivalents
    -Try to make sense of the resulting concatenation of ASCII character values.

    This may not work for all Hex data, of course.

    Nathan H.O.
    Moderator
    SQL-Server-Performance.com

Share This Page