Replace Function does not work on Text Datatype?

• lperrine

  SSC Veteran

  Points: 284

  More actions

  January 5, 2004 at 12:56 pm

  #106971

  I am trying to use the replace function on a text datatype but I am getting the error as follows:

  Server: Msg 8116, Level 16, State 1, Line 1

  Argument data type text is invalid for argument 1 of replace function.

  Here is my query: ( I am trying to replace " with an actual ")

  select replace(comments, '"', '"')

  from tblcaseinfo

  where comments like '%"%'

  Does someone know if this is the problem?

   

  Thanks

• ThomasH

  SSCertifiable

  Points: 7437

  More actions

  January 5, 2004 at 1:07 pm

  #488700

  If ALL your text data is less than 8000 bytes per record, then you could

   

  select replace(Convert(VarChar(8000), comments), '"', '"')

  from tblcaseinfo

  where PatIndex('%&quot;%', comments) <> 0

   

  Notice PATINDEX for your search condition.

  If it's longer, then you have to use UPDATETEXT. see BOL.

  
  
  Once you understand the BITs, all the pieces come together

• lperrine

  SSC Veteran

  Points: 284

  More actions

  January 5, 2004 at 1:11 pm

  #488701

  Much Appreciated!