[FIXED] How to perform integer arithmetic to a string in MySQL?

Issue

I need to increase a value by 1 in MySQL that isn't saved as an Integer: It is saved instead as a String because it may start 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 the 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 to begin til a length of 14 chars.

Can anyone help me with the syntax?

Solution

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')

Answered By - ScaisEdge

Answer Checked By - Dawn Plyler (PHPFixing Volunteer)