Digital Media Insights for Business Owners

Replacing a Character in a MySQL table using REPLACE

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!

Say: "Hola" to your new clients.

Follow us on Social Media for more Tips & Tricks.

Other Posts You May Enjoy