We help IT Professionals succeed at work.

SQL Update with special characters

red_75116
red_75116 asked
on
I am trying to update some values in sql that shouldn't have single quotes but I cant seem to find a way to do an update.

Example

I would like to replace Sam's
with Sams
and the
update table
set field = 'Sams'
where field = 'Sam's'

doesn't work.
Comment
Watch Question

What errors occur?
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
For single quote marks replace with two single quote marks.  For other characters you'd have to use ESCAPE.
____

create table foo (my_name varchar(20))

INSERT INTO foo VALUES('Sams')
INSERT INTO foo VALUES('Sam''s')

SELECT * FROM foo

UPDATE foo
SET my_name = 'Sams'
WHERE my_name = 'Sam''s'   -- <-- Two single quote marks

SELECT * FROM foo
HainKurtSr. System Analyst

Commented:
try this to update all

update mytable
set field = replace(field,'''','')
Commented:
Try using char() function.

update table set field = replace(field,char(39),'')

char(39)  = '

All char codes you will find here (Dec) http://www.asciitable.com/
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
red_75116 - Did you try either my or HainKurt's solution?

Author

Commented:
I did but his seems to be the better solution.  I have about 600 unique values to update and his solution is the best solution.  Thanks!
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Sounds like a plan.  Just checking.

EdgarPoe - Nice link btw