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
– StanislavL, Commented Apr 1, 2014 at 8: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
– Makan, Commented May 3, 2017 at 7:26

Community · Accepted Answer · 2017-05-23 12:34:24Z

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, 2017 at 12:34

CommunityBot

11 silver badge

answered Apr 1, 2014 at 8:34

Sky

3,3602 gold badges17 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lemon Over a year ago

Thank you - the easiest solution, I'm glad I can use ZEROFILL. I have it all working now as MEDIUMINT(4) ZEROFILL

Ravinder Reddy · Accepted Answer · 2014-04-01 08:36:04Z

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

e9_ · Accepted Answer · 2014-04-01 08:36:13Z

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 1, 2014 at 8:36

e9_

1741 silver badge5 bronze badges

Collectives™ on Stack Overflow

Adding leading zero to MySQL data

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related