How to perform integer arithmetic to a string in MySQL?

Question

I need to increase by 1 a value in MySQL that isn't saved as an Integer: It is saved instead as a String because it may starts with a 0, for instance: 01212120211112. I want to set this value to 01212120211113.

The "number" is always 14 char long (including the 0 on beginning) and it will never get longer or shorter.

1. I want to convert it to an Integer
2. set it + 1
3. Convert back to String
4. add a 0 on begin til a lenght of 14 chars.

Can anyone help me with the syntax?

Answer 1

Do the fact you have always a fixed length string You could use convert .. and lpad see the various function in sample

 select CONVERT('01212120211112',UNSIGNED INTEGER)

.

 select CONVERT('01212120211112',UNSIGNED INTEGER) + 1 

.

 select lpad(CONVERT('01212120211112',UNSIGNED INTEGER) + 1 , 14, '0')