2

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;

2 Answers 2

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) < 4;

For example:

+------------------+
| lpad('12',4,'0') |
+------------------+
| 0012             |
+------------------+
1
  • Awesome, this was what I originally thought of but, found the case example and went with that. Thanks to both of you for your solutions. Commented Feb 6, 2017 at 3:49
3

If you really want to concatenate strings: MySQL doesn't use + as a text concatenation operator. So the answer to "what I am doing wrong?" is "you are assuming + concatenates strings (texts) when it doesn't".

You need to use the concat function instead:

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

You can check the different results of using + (or the standard ||) in this SQLFiddle. Notice that mySQL doesn't complain when you user + with strings, it just gives you an unexpected result. (Check what happens when you use SELECT '1' + '2' ; you will feel the effects of something called coercion).

Check also Mysql concatenation operator at SO.

As an alternative, @McNets answer provides a "fit for purpose" function, which is a much shorter solution.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.