The latest replacement I had to do was to change all occurrences of an apostrophe with a single quote in a table. Now, that wouldn’t be a lot of fun to do by hand and believe me, with the number of records it would also be exhausting! So, this is a very quick post that shows you how to update a table, using the REPLACE function in MySQL to change one character:
1. First go ahead and find out how many records are going to be affected:
SELECT * FROM table1 WHERE column1 LIKE "%\'%";
2. Once you know the number of records then simply run the update routine:
UPDATE table1 SET column1 = REPLACE(column1, "\’", "\'") WHERE column1 LIKE "%\’%"
That’s it – very simple. We are updating the table1 and setting the column1 to be equal to the result of a replace. The replace function takes three parameters: 1. The column, 2. The replacement pattern (in this case escaped by a backslash), and 3. The character to replace with – in this case, also escaped with backslash.
The result is a fine looking column of data with your new characters…
Enjoy!
