Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2013
    Posts
    2

    Exclamation Unanswered: SQL Replace on Varbinary Field

    I need to run a replace on a varbinary field but I am unsure how. I have tried mulitple different methods and to no avail. I am stumped.

    I have attached a zip folder that contains a spreadsheet that shows what I am trying to do. In the first tab it shows the SQL command I use to call the field I want to change. In this case it's the Content field and I am calling it using the content_key. As you can tell the content is stored as varbinary. In the second sheet, It shows this varbinary being converted to varchar(max) and it shows what the actual text says.

    The table that this information is stored is is called digitalassetcontent

    I need to remove the
    Code:
    <STYLE>H1{font-weight:bold}H1{font-size: 14pt}OL</STYLE><H1>Product Description</H1>
    that is in front of the actual product description.

    Somehow I need to run this command:
    Code:
    Update digitalassetcontent
    set content = replace(CAST(content as varchar(max)), '<STYLE>H1{font-weight:bold}H1{font-size: 14pt}OL</STYLE><H1>Product Description</H1>','')
    where CAST(content as varchar(max)) like '<STYLE>H1{font-weight:bold}H1{font-size: 14pt}OL</STYLE><H1>Product Description</H1>%' AND content_key = 'desc214974236480438500781058983745755010'
    This is the error message I get when running this code: Implicit conversion from data type varchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query.

    On the varbinary field. How do I do this? I am completely stumped!

    Any help is greatly appreciated.
    Attached Files Attached Files

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    DECLARE @t TABLE (
       content      VARBINARY(MAX)
       )
    
    INSERT @t (content)
       SELECT Cast('This is a test, it is only a test!' AS VARBINARY(MAX))
    
    SELECT Cast(content AS VARCHAR(MAX)) AS 'Before VARCHAR(MAX)'   FROM @t
    SELECT      content                  AS 'Before VARBINARY(MAX)' FROM @t
    
    UPDATE @t
       SET content = Cast(Replace(content, 'test', 'TEST') AS VARBINARY(MAX))
    
    SELECT Cast(content AS VARCHAR(MAX)) AS 'After  VARCHAR(MAX)'   FROM @t
    SELECT      content                  AS 'After  VARBINARY(MAX)' FROM @t
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As a side note, this would be better all around if you changed the column type to VARCHAR(MAX) instead of VARBINARY(MAX). There are potential issues with collation and UTF that get really ugly to detect and/or fix and using VARCHAR(MAX) will avoid those completely as well as fixing simple problems like this.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Dec 2013
    Posts
    2
    Pat,

    After overlooking your code. I am confused as to how this will replace '<STYLE>H1{font-weight:bold}H1{font-size: 14pt}OL</STYLE><H1>Product Description</H1>%' with ' ' only for content_key = 'desc214974236480438500781058983745755010'

    How does it know this is in the digitalassetcontent table?

    Also, based on what I'm trying to accomplish, what should I be putting in for: 'This is a test, it is only a test!'

    Thanks

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I was only demonstrating how to do a replace within a VARBINARY column... You will need to decide how to use that ability within whatever code you are using now.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

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