0

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

Community
  • 1
  • 1
  • Someone in SQL Chat just helped me out. I needed to cast my field to a VARCHAR. It didn't like that it was a text field. –  Dec 19 '12 at 17:58
  • Are you passing these strings from a code (like c sharp) or running this on sql server management studio? – Kaf Dec 19 '12 at 17:59
  • you can surround the expression with double quotes to escape the single quote – Matanya Dec 19 '12 at 17:59

3 Answers3

7

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');
SPFiredrake
  • 3,852
  • 18
  • 26
1

That's funny. I just ran into this exact thing a few minutes ago. It turns out I had to change this:

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

to this: UPDATE TableName SET FieldName = REPLACE(cast(FieldName as varchar(5000)), 'SearchFor', 'ReplaceWith');

sjramsay
  • 555
  • 1
  • 5
  • 12
-1

You must escape the single quote with a slash.

UPDATE TableName
SET FieldName = REPLACE(FieldName, 'Isn\'t', 'Is not');
Blaise Swanwick
  • 1,735
  • 1
  • 16
  • 18