1

I have a long list of insert query which has a slight error. 

INSERT INTO `delivery_zip` (..) 
VALUES ("AB'C / DEF", ..), ("AB'C / DEF", ..), ("AB'C / DEF", ..), ...

How do I remove the single quote after AB' from the values.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sandeep Pariyar
  • 133
  • 2
  • 12
  • 1
    Is the above `INSERT` statement literally how you are inserting the data? What would be wrong with doing a find and replace in a text editor? – Tim Biegeleisen Aug 20 '16 at 08:32
  • You must be building this query using some kind of programming language like java , C#, ..etc . Try to write parameterized query or try to replace single quote while preparing query, doing the same in sql is not a good practice. – Anupam Singh Aug 20 '16 at 08:39

1 Answers1

3

If the single quote in question is the only single quote present in the column col1, and you have already inserted the data, then you should be safe using UPDATE with REPLACE to remove it:

UPDATE delivery_zip
SET col1 = REPLACE(col1, ''', '')

But if you haven't done the insertion yet, you could do a find and replace in your script first, possibly using a regex if needed.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360