Can MySQL replace multiple characters?

by

You can chain REPLACE functions:

select replace(replace('hello world','world','earth'),'hello','hi')

This will print hi earth.

You can even use subqueries to replace multiple strings!

select replace(london_english,'hello','hi') as warwickshire_english
from (
    select replace('hello world','world','earth') as london_english
) sub

Or use a JOIN to replace them:

select group_concat(newword separator ' ')
from (
    select 'hello' as oldword
    union all
    select 'world'
) orig
inner join (
    select 'hello' as oldword, 'hi' as newword
    union all
    select 'world', 'earth'
) trans on orig.oldword = trans.oldword

I’ll leave translation using common table expressions as an exercise for the reader 😉

More Related Contents:

  1. How to find similar results and sort by similarity?
  2. SQL order string as number
  3. MySQL search and replace some text in a field
  4. Replace Into Query Syntax
  5. MySql – Way to update portion of a string?
  6. What are practical differences between `REPLACE` and `INSERT … ON DUPLICATE KEY UPDATE` in MySQL?
  7. MySQL for replace with wildcard
  8. Strip last two characters of a column in MySQL
  9. MySql Query Replace NULL with Empty String in Select
  10. SQL Print the name of all Employees together with the name of their supervisor
  11. MySQL – Rows to Columns
  12. ROW_NUMBER() in MySQL
  13. How can I temporarily disable a foreign key constraint in MySQL?
  14. MySQL offset infinite rows
  15. Ordering by the order of values in a SQL IN() clause
  16. SQL Selecting from two Tables with inner join and limit [duplicate]
  17. MySQL Group by SUM
  18. Get the latest date from grouped MySQL data
  19. In SQL, how to select the top 2 rows for each group
  20. Mysql query to dynamically convert rows to columns on the basis of two columns
  21. Invalid default value for ‘dateAdded’
  22. How to set a MySQL row to READ-ONLY?
  23. Split a string and loop through values in MySQL stored procedure
  24. How to create dummy variable columns for thousands of categories in Google BigQuery?
  25. Most efficient way to store IP Address in MySQL [duplicate]
  26. SQL date format convert? [dd.mm.yy to YYYY-MM-DD]
  27. INSERT INTO Table from multiple tables
  28. Execute INSERT if table is empty?
  29. Mysql How do you create a clustered index?
  30. How do I extract Month and Year in a MySQL date and compare them?

Leave a Comment