MySQL – How to Prefix Values Shorter Than X

MySQLmysql-5.7

I have a table that uses an integer for an id. I need to change this to a String and prefix all entries shorter than 4 with zeroes. The database is MySql. I converted the field to a VARCHAR(6), but not need to convert the data in the column to have it prefixed. I tried the following code, but it did not update any of the rows. Any help as to what I am doing wrong would be appreciated.

UPDATE clients
SET clientId = 
    CASE 
    WHEN char_length(clientId) = 1 THEN '000' + clientId 
    WHEN char_length(clientId) = 2 THEN '00' + clientId
    WHEN char_length(clientId) = 3 THEN '0' + clientId
    ELSE clientId END
WHERE
    char_length(clientId) < 4;

Best Answer

Use LPAD function.

LPAD(str,len,padstr)

Returns the string str, left-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters.

UPDATE clients
SET clientId = LPAD(clientId, 4, '0') 
WHERE
    char_length(clientId) < 4;

For example:

+------------------+
| lpad('12',4,'0') |
+------------------+
| 0012             |
+------------------+
Related Question