SQL HowTo: замена в строке по набору

Решим сегодня простую, казалось бы, задачу: как на PostgreSQL можно в строке провести замены по набору пар строк. То есть в исходной строке 'abcdaaabbbcccdcba' заменить, например, {'а' -> 'x', 'bb' -> 'y', 'ccc' -> 'z'} и получить 'xbcdxxxybzdcbx'.

Фактически, мы попробуем создать аналог str_replace или strtr.

Callback Hell

Первое, что приходит на ум — это сделать цепочку из вложенных вызовов replace:

SELECT   replace( -- ... и так 100500 раз     replace(       replace(         'abcdaaabbbcccdcba' -- исходная строка       , 'a'       , 'x'       )     , 'bb'     , 'y'     )   , 'ccc'   , 'z'   );

Такой код настолько же эффективен, насколько и нерасширяем.

Рекурсия

Зайдем на проблему с другой стороны.

Мы хотим последовательно в цикле заменять одну подстроку на другую — а за «циклы» в SQL отвечает рекурсия:

WITH RECURSIVE rpl AS (   SELECT     row_number() OVER() i -- нумеруем наши замены   , *   FROM     (       VALUES -- список замен теперь легко расширяем         ('a',   'x')       , ('bb',  'y')       , ('ccc', 'z')     ) T(f, t) ) , R AS (   SELECT     1::bigint i   , 'abcdaaabbbcccdcba' s -- исходная строка UNION ALL   SELECT     i + 1   , replace(R.s, rpl.f, rpl.t) -- заменяем i-ю пару   FROM     R   NATURAL JOIN -- USING(i)     rpl ) SELECT   s FROM   R ORDER BY   i DESC -- возвращаем результат последнего шага LIMIT 1;

strtr

Оба эти варианта обладают неприятной особенностью повторной замены — то есть каждый следующий шаг опирается на результат предыдущего, как в str_replace. Поэтому при замене исходной строки 'aaa' с набором {'a' -> 'b', 'b' -> 'c'} мы получим 'ccc', а вовсе не 'bbb'.

Чтобы обойти этот недостаток, воспользуемся разбиением строки сразу по всему набору заменяемых подстрок с помощью регулярных выражений:

WITH src(s) AS (   VALUES('abcdaaabbbcccdcba') -- исходная строка ) , rpl AS (   SELECT -- набор замен в виде json-объекта     '{        "a"   : "x"      , "bb"  : "y"      , "ccc" : "z"      }'::json ) , rpl_re AS (   SELECT     '(' || string_agg(k, '|') || ')' re -- '(a|bb|ccc)'   FROM     json_object_keys((TABLE rpl)) k -- получаем все ключи для замен ) , spl AS (   SELECT     T.*   FROM     src   , rpl_re   , unnest( -- совместный unnest двух разноразмерных массивов       regexp_split_to_array(s, re) -- тут части "между" ключами     , ARRAY( -- тут сами ключи         SELECT           m[1]         FROM           regexp_matches(s, re, 'g') m       )     ) T(part, key) ) SELECT   string_agg(concat(part, (TABLE rpl) ->> key), '') -- подставляем найденные ключи по словарю замен FROM   spl;

В нашем примереunnest(regexp_split_to_array, ARRAY(regexp_matches[1])) вернет следующий результат:

part | key  --- | a  bcd | a  --- | a  --- | a  --- | bb    b | ccc  dcb | a  --- | ---

После чего нам только остается для каждого ключа выполнить подстановку на целевое значение и собрать строку обратно через string_agg.

Вот и все!