Replacing a Character in a MySQL table using REPLACE

November 24th, 2014 admin Location: Raleigh, NC, Topic: Web Design | Digital Media

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!

About the Author

Alex Centeno MBA., is an international creative director and digital media strategist. Currently residing minutes away from the Research Triangle Park (RTP) - North Carolina, and with over 10 years of interactive marketing training and experience, Alex leads Merkados' international clients to maximizing their online business strategies. One of Alex's biggest strengths is his world-class capacity to effectively combine interactive marketing, digital media design and web development.

You may find further information about Alex Centeno MBA. at: and Twitter.

Sobre el Autor

Alex Centeno MBA., Es un director creativo y estratega de medios digitales reconocido internacionalmente. En la actualidad reside a tan solo minutos del Research Triangle Park (RTP) - Carolina del Norte, y con más de 10 años de entrenamiento y experiencia en marketing interactivo, Alex lidera a los clientes internacionales de Merkados™ para maximizar sus estrategias de negocios en línea. Una de las mayores fortalezas de Alex es su capacidad global de combinar de manera efectiva, mercadeo interactivo, diseño de medios digitales y desarrollo web.

Para más información sobre Alex Centeno MBA. puede visitar: y Twitter.