Search code examples
sql-serverstringapostrophe

How to use REPLACE with string containing apostrophe?

Possible Duplicate:
Replace single quote in sql server

So I'm trying to use the following code:

UPDATE TableName
SET FieldName = REPLACE(FieldName, 'SearchFor', 'ReplaceWith');

It works wonderfully for what I need to do, except for the fact that what I need to search for is "valid driver's license". That apostrophe doesn't seem to agree with the code. I tried doing this:

'valid driver''s license'

...but that doesn't seem to work either. I'm getting the following error:

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

If anyone has dealt with this before, I would love some help! This would save me so much time, rather than updating each record by hand. -__-

Ellie

Solution

  • The error tells you exactly what the problem is. FieldName is a column of type text, which doesn't work with the REPLACE function. Try casting the first param as VARCHAR(MAX) first, and it should work. IE:

    UPDATE TableName
SET FieldName = REPLACE(CAST(FieldName AS VARCHAR(MAX)), 'SearchFor', 'ReplaceWith');