Remaining leading zero after adding in sql

When employee_id is added by one, leading zeros are removed in the following query.

SELECT SUBSTRING(MAX(ID), 6, 4)+1 FROM `employee`

Here is my table.

ID        | Name
=================
Empl_0001 | Alex
Empl_0002 | John

How can I remain leading zeros?

Answer

Option 1:

You can use LPAD. here is the demo. this will keep your ID length of 4.

SELECT 
    LPAD( SUBSTRING(MAX(ID), 6, 4) + 1 , 4, '0') as ID
FROM myTable

Option 2: You can use concat and here is the demo.

SELECT 
  concat('000', SUBSTRING(MAX(ID), 6, 4)+1) 
FROM myTable

Output:

| ID   |
| ---- |
| 0003 |