1

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.

Community
  • 1
  • 1
Geoffrey Ochsner
  • 215
  • 2
  • 14
  • 2
    don't look for the number. Look for what's between [ and ]. cast it to number add + 1 and combine it back in the string. – xQbert Jun 20 '14 at 17:26

2 Answers2

1

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.

Bill Karwin
  • 462,430
  • 80
  • 609
  • 762
1

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

Fabricator
  • 12,381
  • 2
  • 23
  • 36