Skip to content
Advertisement

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;

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
5 People found this is helpful
Advertisement