CODEWITHSUNDEEP
mysql
Ludovic Guillaume
Ludovic Guillaume

Reputation: 3287

Increment string in MySQL

I'd like to increment a string value in MySQL. It will be used in a query to get the first available value.

I tried CONV(...) with base 36 but it does not cover every case. The string can have a different structure depending on some internal parameters.

Possibles structures :

  pattern                   |    sample    |    incremented
----------------------------|--------------|-------------------
 ([0-9]+)                   |        1239  |           1240
 ([A-Z]+)                   |        ABCD  |           ABCE
 ([0-9]+)([A-Z]+)           |       1234A  |          1234B
 ([A-Z]+)([0-9]+)           |       ABCD1  |          ABCD2
 ([A-Z]+)-([0-9]+)-([A-Z]+) | ABCD-1239-Z  |    ABCD-1240-A
 etc.                       |              |

Is it possible in MySQL?

Upvotes: 2

Views: 2241

Answers (1)

Seb
Seb

Reputation: 1551

A short answer: No.

Long answer: You could write a stored procedure that does this (or do it with PHP/JAVA/...). But still it's difficult because there is no clear rule how to increment a string.

Take your last example: ABCD-1239-Z to ABCD-1240-A You need a rule that looks for - in the string and never changes this, but changes the chars before, handling them as numbers!

Let me give another possible pattern (something like this will probably happen): Z-Z How to increase this? Will it become AA-A or 1A-A or Z-AA?

If you need to increment, than stick to a number. Everything else will get you into trouble.

Upvotes: 5

Related Questions