Increment string in MySQL

Question

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?

Why, for the sake of anything that's holy in this world of computers would you want to "increment" a string? Anything is possible, the question is what are you willing to give up for it to work? — N.B.
– N.B., Commented Dec 18, 2013 at 9:23
I need this to get the first available value in a column for a given pattern. I don't know what I'm willing to give up actually, maybe all that seems reasonable. — Ludovic Guillaume
– Ludovic Guillaume, Commented Dec 18, 2013 at 9:35
This is an XY problem. You arrived at something that you think is a solution, and you're looking for the means to achieve it. However, as always, you probably chose the wrong approach. The real question is why do you need first available value in a column? What is it that you're doing really? You are forgetting that databases are concurrent systems, which means that there are many connections operating on the same data. What about ensuring you don't get duplicate records? You need to worry about many factors, I'd suggest explaining your problem in detail and look for a different approach. — N.B.
– N.B., Commented Dec 18, 2013 at 10:46
Here's some explanation but that's actually not the question so I won't edit my post. These values are used to identify a person (but not used as PK). There's different types of persons and each type has a pattern (like described above). I need the first available value to suggest it to the user that wants to add a person. I think the fact that databases are concurrent systems is not an issue, my back-end (j2ee) can lock/release the method call if needed - please correct me if I'm wrong. By the way, thanks for your answer! — Ludovic Guillaume
– Ludovic Guillaume, Commented Dec 18, 2013 at 11:26

Seb · Accepted Answer · 2013-12-18 08:55:44Z

5

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.

answered Dec 18, 2013 at 8:55

Seb

1,5511 gold badge12 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ludovic Guillaume Over a year ago

I'm already writting a stored procedure to get the first available value so this will be included. The rule is the regular expression actually. You are right about the pattern you gave, didn't think about that. The pattern should be more specific like this ([A-Z]+)-([A-Z]{1}) for example. You make me think that it won't be possible in MySQL but I keep looking for.

Ludovic Guillaume Over a year ago

By the way, I can't stick to numbers. It would be really simpler otherwise.

Peter Chaula Over a year ago

Using PHP, it's trivial. It allows you do use the increment operator on the string

Collectives™ on Stack Overflow

Increment string in MySQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related