Thursday, January 10, 2013

SQL Multi Column Search and Replace/Update Queries

I had coded something wrong.  I was searching and replacing a quote with / when inserting in to the database.  Problem I ran into is if you actually needed to use the / it replaced it with a quote.  After learning there are specific PHP libraries for escaping specific characters before inserting in to a database, I now needed to go back and correct my inserted data.  Now the amount of data I had to deal with was on the small side 200-300 rows, but either way to fix it by hand would take for ever.   Here is how I did it with SQL.

If you want to first search for a specific set of criteria:

select * from tablex where company like '%/%' or item like '%/%' or  description like '%/%';

How to search multiple columns:

update tablex set vendor = REPLACE(vendor, "/s", "'s"), item = REPLACE(item, "/s", "'s"), description = REPLACE(description, "/s", "'s")

How to search same column, but different search critera:

update tablex set description = REPLACE(description, "Men/s", "Men's"), description = REPLACE(description, "Woman/s", "Woman's")

Source:  Techonthenet