Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2009
    Posts
    8

    Unanswered: how to convert hexa string to varbinary?

    hi
    I have this script to get the default value of a column dynamically ,it works for most columns types except for varbinary that has default hex value, when @t = 0x0343 ,then @v =0x03
    note that I had to use nvarchar(4000) at first because INFORMATION_SCHEMA.COLUMNS forces me to do so, can you help me? thanks in advance

    Code:
     
    -----------------------------------
    create table #temp
    (
    column_name sysname,
    column_def nvarchar(4000)
    )
    
    insert into #temp SELECT column_name,column_default
    
    FROM INFORMATION_SCHEMA.COLUMNS
    where table_Name ='table_1'
    
    declare @t nvarchar(4000)
    set @t=(select column_def from #temp where column_name='c_varbin')
    set @t = replace (@t ,'(','')
    set @t = replace (@t ,')','')
    
    
    
    select @t
    
    declare @v varbinary
    set @v = convert ( varbinary,@t)
    
    select @v
    drop table #temp
    -----------------------------------

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    In the lines
    Code:
    declare @v varbinary
    set @v = convert ( varbinary,@t)
    You likely need to specify a length for the varbinary variable, or it will default to 1 byte.

  3. #3
    Join Date
    Jun 2009
    Posts
    8
    no ,it didn't work

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •