CODEWITHSUNDEEP
mysqlsubstring
Geoffrey Ochsner
Geoffrey Ochsner

Reputation: 235

Find number value in string and increment it using replace

I have a column of text. The text may contain the string "var[1]" where 1 can be any number. I would like to increment the number inside the brackets. So if my first row of this column contained text that looked like:

Some text followed by var[1] which has other text followed by var[0] and lastly var[2]

My new value would be

Some text followed by var[2] which has other text followed by var[1] and lastly var[3]

I assume it's going to require an update with a replace like this question. Update a column value, replacing part of a string

I'm just not sure how to use a regex to find and increment the number.

Upvotes: 1

Views: 946

Answers (2)

Fabricator
Fabricator

Reputation: 12782

Yet another implementation of the same idea..

select 
  v, 
  concat(
    left(v,locate('[',v)),
    mid(v, locate('[',v)+1, locate(']',v)-locate('[',v)-1)+1,
    right(v,length(v)-locate(']',v)+1)) v2
from table1;

fiddle

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562573

Here's an implementation of the idea mentioned by @xQbert in comments:

mysql> set @v = 'var[1]';
mysql> select concat(substring_index(@v, '[', 1), '[', 
    substring_index(substring_index(@v, ']', 1), '[', -1)+1, 
    ']', substring_index(@v, ']', -1)) as newv;
+--------+
| newv   |
+--------+
| var[2] |
+--------+

This demo uses a variable @v but you can of course use a column name from a table in the same way.

Upvotes: 1

Related Questions