Как заменить каждый другой экземпляр определенного символа в строке MySQL?

Вы должны рассмотреть возможность хранения ваших данных в нормализованной схеме. В вашем случае таблица должна выглядеть так:

 | id | k | v | |----|---|----------| | 1 | A | 10 | | 1 | B | 20 | | 1 | C | 30 | | 2 | A | Positive | | 2 | B | Negative | 

Эта схема более гибкая, и вы поймете, почему.

Итак, как преобразовать данные в новую схему? Вам понадобится вспомогательная таблица, содержащая порядковые номера. Поскольку ваш столбец является varchar(255) вы можете хранить только 128 значений (+ 127 разделителей). Но давайте просто создадим 1000 номеров. Вы можете использовать любую таблицу с достаточным количеством строк. Но поскольку любой сервер MySQL имеет таблицу information_schema.columns , я буду использовать его.

 drop table if exists helper_sequence; create table helper_sequence (i int auto_increment primary key) select null as i from information_schema.columns c1 join information_schema.columns c2 limit 1000; 

Мы будем использовать эти числа в качестве позиции значений в вашей строке, объединив эти две таблицы.

Чтобы извлечь значение из строки с разделителями, вы можете использовать функцию substring_index() . Значение в позиции i будет

 substring_index(substring_index(t.options, '|', i ), '|', -1) 

В вашей строке есть последовательность ключей, за которыми следуют ее значения. Позиция ключа – нечетное число. Поэтому, если позиция ключа равна i , позиция соответствующего значения будет равна i+1

Чтобы получить количество разделителей в строке и ограничить наше соединение, мы можем использовать

 char_length(t.options) - char_length(replace(t.options, '|', '')) 

Запрос для хранения данных в нормализованной форме:

 create table normalized_table select t.id , substring_index(substring_index(t.options, '|', i ), '|', -1) as k , substring_index(substring_index(t.options, '|', i+1), '|', -1) as v from old_table t join helper_sequence s on si <= char_length(t.options) - char_length(replace(t.options, '|', '')) where si % 2 = 1 

Теперь запустите select * from normalized_table и вы получите следующее:

 | id | k | v | |----|---|----------| | 1 | A | 10 | | 1 | B | 20 | | 1 | C | 30 | | 2 | A | Positive | | 2 | B | Negative | 

Итак, почему этот формат является лучшим выбором? Помимо многих других причин, одним из них является то, что вы можете легко преобразовать его в свою старую схему с помощью

 select id, group_concat(concat(k, '|', v) order by k separator '|') as options from normalized_table group by id; | id | options | |----|-----------------------| | 1 | A|10|B|20|C|30 | | 2 | A|Positive|B|Negative | 

или в желаемый формат

 select id, group_concat(concat(k, '|', v) order by k separator ',') as options from normalized_table group by id; | id | options | |----|-----------------------| | 1 | A|10,B|20,C|30 | | 2 | A|Positive,B|Negative | 

Если вы не заботитесь о нормализации и просто хотите, чтобы эта задача выполнялась, вы можете обновить таблицу с помощью

 update old_table o join ( select id, group_concat(concat(k, '|', v) order by k separator ',') as options from normalized_table group by id ) n using (id) set o.options = n.options; 

И отмените normalized_table .

Но тогда вы не сможете использовать простые запросы, такие как

 select * from normalized_table where k = 'A' 

См. Демонстрацию на rextester.com

Не используя хранимые процедуры, я бы сделал это в 2 этапа:

1. Вставьте запятую во второе вхождение символа трубы:

    update options set options = insert(options, locate('|', options, locate('|', options) + 1), 1, ','); 

2. Вставьте остальные запятые – выполните запрос N раз:

    update options set options = insert(options, locate('|', options, locate('|', options, length(options) - locate(',', reverse(options)) + 1) + 1), 1, ','); 

   где N =

    select max(round(((length(options) - length(replace(options, '|', ''))) - 1 ) / 2) - 1) from options; 

   (или не беспокоиться о подсчете и продолжать выполнять запрос, если он не говорит вам «0 строк затронуты»)

Проверено этим набором данных:

 id options 1 A|10|B|20|C|30 2 A|Positive|B|Negative 3 A|10|B|20|C|30|D|40|E|50|F|60 4 A|Positive|B|Negative|C|Neutral|D|Dunno 

результаты:

 id options 1 A|10,B|20,C|30 2 A|Positive,B|Negative 3 A|10,B|20,C|30,D|40,E|50,F|60 4 A|Positive,B|Negative,C|Neutral,D|Dunno 

(Я объясню позже)

демонстрация

Демо-версия реестров

объяснение

Это можно было бы решить относительно легко, если бы у MySQL была функция замены регулярных выражений, но, к сожалению, это не так . Поэтому я попытался написать его . Он состоит из хранимой процедуры и функции и неизбежно имеет некоторую степень сложности, но является полезной «библиотечной функцией», которая уже успешно ответила на многие другие вопросы StackOverflow. Несмотря на то, что он всегда мог делать с большим количеством тестов, он до сих пор оказался полезным и надежным.

Для ответа на этот вопрос требуется слегка измененная версия оригинала – такая же, как и здесь . Это позволяет выполнять рекурсивную замену в найденном совпадении для замены. Тогда можно использовать следующий относительно простой SQL:

SQL

 SELECT id, options AS `before`, reg_replace(options, '\\|.*\\|', -- 2 pipe symbols with any text in between '\\|$', -- Replace the second pipe symbol ',', -- Replace with a comma FALSE, -- Non-greedy matching 2, -- Min match length = 2 (2 pipe symbols) 0, -- No max match length 0, -- Min sub-match length = 1 (1 pipe symbol) 0 -- Max sub-match length = 1 (1 pipe symbol) ) AS `after` FROM tbl; 

Хм, я думаю, вы пытаетесь сделать что-то подобное

 SELECT GROUP_CONCAT(CONCAT(options,",") SEPARATOR "|") FROM Table.name; 

Я кратко объясняю, беру за каждую строку результат, и я конкатенирую «,», и я объединяю всю строку с разделителем «|». Вам нужно будет изменить Table.name с именем вашей таблицы

Если вы хотите объединить еще одно значение, например A, B, C (вы не объяснили, откуда взялось значение ABC, давайте предположим ValueWhereABCisComingFrom):

 SELECT GROUP_CONCAT(CONCAT(ValueWhereABCisComingFrom,"|",options) SEPARATOR ",") FROM Table.name; 

если моя таблица выглядит так:

 id | ValueWhereABCisComingFrom | options 0 | A | 10 1 | B | 20 2 | C | 30 

У вас есть что-то вроде этого:

 A|10,B|20,C|30 

ИЗМЕНИТЬ 1

В этом случае нет способа сделать это. В mysql нет функции, например preg_replace. Все, что вы можете сделать, это заменить все «|» как

 SELECT Replace(options, '|', ',') AS P FROM `docs`; 

В MariaDB есть такая функция, поэтому вы можете попробовать перейти от одной базы к другой. Но только с MYSQL, никоим образом: /

Вы можете сделать, создав функцию

 CREATE FUNCTION doiterate(str TEXT, i INT, next INT, isp TINYINT(1)) RETURNS TEXT BEGIN myloop: LOOP IF next = 0 THEN LEAVE myloop; END IF; IF isp = TRUE THEN set str = insert(str, i, 1, ','); set isp = FALSE; set i = next; set next = locate('|', str, i + 1); ITERATE myloop; ELSE set isp = TRUE; set i = next; set next = locate('|', str, i + 1); ITERATE myloop; END IF; LEAVE myloop; END LOOP; return str; END; 

и называя это так:

 SELECT t.`column`, @loc := locate('|', t.`column`) as position, @next := locate('|', t.`column`, @loc +1) as next, @isp := 0 is_pipe, @r := doiterate(t.column, @loc, @next, @isp) as returnstring from test t; 

Я предполагаю, что вы будете достаточно умны, чтобы

• изменить имя табуляции и имя столбца
• вставить это в запрос на обновление

Вы можете изменить @isp: = на 1, если я получил неправильное изменение трубы / комы (я предполагал, что вторая труба должна быть заменена на кому)