# SQL - PostgreSQL - Multi-Replace (plpgsql)
*Source: https://wiki.postgresql.org/wiki/Multi_Replace_plpgsql*
*NOTE: This function is generic [[SQL]]:*
```SQL
/* This function quotes characters that may be interpreted as special in a regular expression.
It's used by the function below and declared separately for clarity. */
CREATE FUNCTION quote_meta(text) RETURNS text AS $
select regexp_replace($1, '([\[\]\\\^\$\.\|\?\*\+\(\)])', '\\\1', 'g');
$ language sql strict immutable;
```
*NOTE: This function uses the [[PLPGSQL]] language.*
```SQL
/* Substitute a set of substrings within a larger string.
When several strings match, the longest wins.
Similar to php's strtr(string $str, array $replace_pairs).
Example:
select multi_replace('foo and bar is not foobar',
'{"bar":"foo", "foo":"bar", "foobar":"foobar"}'::jsonb);
=> 'bar and foo is not foobar'
*/
CREATE FUNCTION multi_replace(str text, substitutions jsonb)
RETURNS text
as $
DECLARE
rx text;
s_left text;
s_tail text;
res text:='';
BEGIN
select string_agg(quote_meta(term), '|' )
from jsonb_object_keys(substitutions) as x(term)
where term <> ''
into rx;
if (coalesce(rx, '') = '') then
-- the loop on the RE can't work with an empty alternation
return str;
end if;
rx := concat('^(.*?)(', rx, ')(.*)