Adding leading zero to MySQL data

Question

I have some messy data in a MySQL table that needs clearing. Everything should be either 3 or 4 characters, and those that are two characters need to have a leading zero.

Example Messy Data:

Which should be:

I've read that I should use either ZEROFILL or LPAD, but as mine vary in length (3-4) I'm not sure if this is the best method, I worry that 4 character data will be cut short or similar. I would appreciate some direction - thank you.

EDIT: I should add, the Type for this column is mediumint(2). I am free to change this if required.

if (length(your_field)<3, lpad('0', your_field), your_field) as new_field_value ? — StanislavL, Apr 01 '14 at 08:31
This is a bad title. It should say, "adding leading zeros in Mysql data presentation". The leading zeros are not here meant to be part of the data. — Makan, May 03 '17 at 07:26

score 1 · Accepted Answer · edited May 23 '17 at 12:34

1

Use ZEROFILL as it would meet your requirement of not truncating your data when the length is more than 3. It will only fill 0 if it's shorter than 3.

What is the benefit of zerofill in MySQL?

edited May 23 '17 at 12:34

Community

1
1

answered Apr 01 '14 at 08:34

Sky

3,350
2
14
12

Thank you - the easiest solution, I'm glad I can use ZEROFILL. I have it all working now as MEDIUMINT(4) ZEROFILL – Lemon Apr 01 '14 at 08:54

score 1 · Answer 2 · answered Apr 01 '14 at 08:36

I worry that 4 character data will be cut short or similar.

No. data will not be cut, unless you specify a padding length lesser than the current length of data.

Using LPAD the given length is ensured

Example:

mysql> SELECT LPAD('hi',4,'??');
        -> '??hi' -- result is a string of length 4
-- result data will be cut if padding is less than data length
mysql> SELECT LPAD('hi',1,'??'); 
        -> 'h' -- result is a string of length 1

On your data you can specify a length equal to max length of a field data. If your data supports a max length of 4 digits, then
Try this:

SELECT field_name fv, LPAD( field_name, 4, '0' ) lfv from my_table;

It would result as below:

+------+------+
| fv   | lfv  |
+------+------+
|   23 | 0023 |
| 5351 | 5351 |
|  142 | 0142 |
|   91 | 0091 |
| 4911 | 4911 |
| 1514 | 1514 |
|   53 | 0053 |
|  931 | 0931 |
|  984 | 0984 |
+------+------+

Refer to: Documentation on LPAD

score 1 · Answer 3 · answered Apr 01 '14 at 08:36

1

LPAD is what you want but you only want to add 0s to numbers that are less than 3 characters long, you can do following:

update TABLE set COLUMN = LPAD(COLUMN, 3, '0') where COLUMN < 100

answered Apr 01 '14 at 08:36

e9_

174
1
5

Adding leading zero to MySQL data

3 Answers3