Replace Function does not work on Text Datatype?

  • 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

  • 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

  • Much Appreciated! 

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply