Can MySQL replace multiple characters?

I'm trying to replace a bunch of characters in a MySQL field. I know the REPLACE function but that only replaces one string at a time. I can't see any appropriate functions in the manual.

Can I replace or delete multiple strings at once? For example I need to replace spaces with dashes and remove other punctuation.

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 ;)

Cascading is the only simple and straight-forward solution to mysql for multiple character replacement.

UPDATE table1 
SET column1 = replace(replace(REPLACE(column1, '\r\n', ''), '<br />',''), '<\r>','')

I've been using lib_mysqludf_preg for this which allows you to:

> Use PCRE regular expressions directly in MySQL

With this library installed you could do something like this:

SELECT preg_replace('/(\\.|com|www)/','','www.example.com');

Which would give you:

example

CREATE FUNCTION IF NOT EXISTS num_as_word (name TEXT) RETURNS TEXT RETURN
(
	SELECT 
		REPLACE(
			REPLACE(
				REPLACE(
					REPLACE(
						REPLACE(
							REPLACE(							
								REPLACE(
									REPLACE(							
										REPLACE(IFNULL(name, ''), 
											'1', 'one'), 
											'2', 'two'), 
											'3', 'three'), 
											'4', 'four'), 
											'5', 'five'), 
											'6', 'six'), 
											'7', 'seven'),															
											'8', 'eight'),
											'9', 'nine')
);

REPLACE does a good simple job of replacing characters or phrases everywhere they appear in a string. But when cleansing punctuation you may need to look for patterns - e.g. a sequence of whitespace or characters in the middle of a word or after a full stop. If that's the case, a regular expression replace function would be much more powerful.

---

UPDATE: If using MySQL version 8+, a REGEXP_REPLACE function is provided and can be invoked as follows:

SELECT txt,
       REGEXP_REPLACE(REPLACE(txt, ' ', '-'),
                      '[^a-zA-Z0-9-]+',
                      '') AS `reg_replaced`
FROM test;

See this DB Fiddle online demo.

---

PREVIOUS ANSWER - only read on if using a version of MySQL before version 8: .

The bad news is MySQL doesn't provide such a thing but the good news is it's possible to provide a workaround - see this blog post.

> Can I replace or delete multiple strings at once? For example I need > to replace spaces with dashes and remove other punctuation.

The above can be achieved with a combination of the regular expression replacer and the standard REPLACE function. It can be seen in action in this online Rextester demo.

SQL (excluding the function code for brevity):

SELECT txt,
       reg_replace(REPLACE(txt, ' ', '-'),
                   '[^a-zA-Z0-9-]+',
                   '',
                   TRUE,
                   0,
                   0
                   ) AS `reg_replaced`
FROM test;

on php

$dataToReplace = [1 => 'one', 2 => 'two', 3 => 'three'];
$sqlReplace = '';
foreach ($dataToReplace as $key => $val) {
	$sqlReplace = 'REPLACE(' . ($sqlReplace ? $sqlReplace : 'replace_field') . ', "' . $key . '", "' . $val . '")';
}
echo $sqlReplace;

result

REPLACE(
	REPLACE(
		REPLACE(replace_field, "1", "one"),
	"2", "two"),
"3", "three");

UPDATE schools SET
slug = lower(name),
slug = REPLACE(slug, '|', ' '),
slug = replace(slug, '.', ' '),
slug = replace(slug, '"', ' '),
slug = replace(slug, '@', ' '),
slug = replace(slug, ',', ' '),
slug = replace(slug, '\'', ''),
slug = trim(slug),
slug = replace(slug, ' ', '-'),
slug = replace(slug, '--', '-');

UPDATE schools SET slug = replace(slug, '--', '-');