I want to get the maximum value of a table and then add that with +1. The problem here is that the column contains leading zeros and the length of the column cannot be greater than 4.
Here an example:
Tablename: car
| id |
|---|
| C001 |
| C002 |
| … |
| C009 |
I have tried to solve this problem with this sql Query:
select concat(left(max(id),3),right(max(id),1)+1) as new from car;
result: C0010 The problem with this query is that if C009 is the largest entry, the query should return C010. With this query, however, it returns C0010.
Advertisement
Answer
SELECT CONCAT(LEFT(id, 1), LPAD(SUBSTRING(id FROM 2) + 1, 3, '0')) AS new FROM car