MySQL: fix data inserted inside quotes in DB

Question

I need to fix some (bad) inserted data in DB, I had data inserted with single qoutes like: ('love')

I want to move this to (love), without effect on something like (we're),

info table (structure)

id     text
1     'love'
2     'man'
3     we're..
4     John
5     'Sarah'

I want to fix "info" table to be:

info table (structure)

id     text
1      love
2      man
3      we're..
4      John
5      Sarah

I can select this data using:

select * from info where text like "'%'"

thanks,

possible duplicate of [Insert a value containg single quotes in MySQL](http://stackoverflow.com/questions/887036/insert-a-value-containg-single-quotes-in-mysql) — Mark, Feb 18 '15 at 09:02

score 3 · Accepted Answer · answered Feb 18 '15 at 09:04

3

try this untested query:

update info set text = replace(text,"'","") where text like "'%'"

answered Feb 18 '15 at 09:04

Jens

Even this is working on MySQL, don't use `"` for string. It will fail on every other, standard compatible system. – frlan Feb 18 '15 at 09:25

Nitin Alapati · Answer 2 · 2015-02-18T09:28:18.457

0

You can use this statement to update all the rows of the table.

update Table_Name
set ColumnName= REPLACE(ColumnName,'''','')
where ColumnName LIKE ('''%''')

edited Feb 18 '15 at 09:28

answered Feb 18 '15 at 09:08

Nitin Alapati

2 Answers2