How to prefix values shorter than X 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) &lt; 4;
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) &lt; 4;

For example:
+------------------+
| lpad('12',4,'0') |
+------------------+
| 0012             |
+------------------+