Write a query to ensure that the Length of all the Cus_Id should be 6.If the length is not 6 then append the required number of ‘0’s are the prefix

Question

how to append prefix if the size of the data is less than 6

SELECT Cust_Id,
CASE 
WHEN LEN(Cust_Id)<6 
THEN CONCAT((SELECT REPLICATE('0',(SELECT LEN(Cust_Id) - 6 ) )), Cust_Id)
ELSE Cust_Id
END as Cust_Id
FROM Customers

Answer 1

you could try using mysql lpad function

select  Cust_Id, lpad(Cust_Id, 6, '0')
FROM Customers

https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_lpad

Answer 2

use LPAD fucntion of MySQL, to add leading number of any char as prefix.

SELECT LPAD(Cust_Id, 6, '0') FROM `Customers`;