Search code examples
mysqlsubstringconcatenation

Removing the first occurrence of n digits in a string in Mysql

I want to remove the first occurrence of 15 from this number. I found this way to do it but surely there is a simpler way.

select concat(Substring_index(0180154571556, '15', 1),Substring(0180154571556,Length(concat(Substring_index(0180154571556, '15', 1),'15'))+1,length(0180154571556))) as text;

--Expected result: 1804571556

Solution 
  • mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.26    |
+-----------+

mysql> select regexp_replace('0180154571556', '15', '', 1, 1) as text;
+-------------+
| text        |
+-------------+
| 01804571556 |
+-------------+

    See https://dev.mysql.com/doc/refman/8.0/en/regexp.html#function_regexp-replace