Answer the question
In order to leave comments, you need to log in
How to replace only the first occurrence in a string?
I need to perform an UPDATE of the row and delete the first occurrence of "11" from the parent field with the value "14 11 10 11 52" (this is an approximate value, in fact, it will be any at all), and leave the second and subsequent ones untouched. How to do it?
Answer the question
In order to leave comments, you need to log in
-- строка, в которой нужно провести поиск
SET @parent = '14 11 10 11 52';
-- строка, которую нужно найти
SET @search = '11';
-- найти начальную позицию нужной строки
SELECT INSTR(@parent, @search);
-- зная расположение и размер подстроки, можно вырезать фрагмент текста
-- первая часть
SELECT SUBSTR(@parent, 1, INSTR(@parent, @search) - 1);
-- хвост
SELECT SUBSTR(@parent, INSTR(@parent, @search) + LENGTH(@search));
-- все вместе
SELECT CONCAT
(
SUBSTR(@parent, 1, INSTR(@parent, @search) - 1),
SUBSTR(@parent, INSTR(@parent, @search) + LENGTH(@search))
);
UPDATE example SET parent = CONCAT
(
SUBSTR(parent, 1, INSTR(parent, '11') - 1),
SUBSTR(parent, INSTR(parent, '11') + LENGTH('11'))
);
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question