SQL mixed String with leading zeros

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;

​x
 
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.

Answer

SELECT CONCAT(LEFT(id, 1), LPAD(SUBSTRING(id FROM 2) + 1, 3, '0')) AS new
FROM car

 
SELECT CONCAT(LEFT(id, 1), LPAD(SUBSTRING(id FROM 2) + 1, 3, '0')) AS newFROM car​

Advertisement

Answer