0

I have some data stored as an unsigned tinyint. When displaying it I want it to be zero padded only if it is 1 digit in length. I have issues with LPAD(CAST(NUMBER AS CHAR CHARSET UTF8),2,'0') when I have three digit long numbers. How do I get what I'm looking for?

Example of data in table:

NUMBER
1
2
3
...
10
11
...
101
102

What I currently get when running with LPAD

LPAD_NUMBER
01
02
03
...
10
11
...
10
10

What I want

UNKNOWN
01
02
03
...
10
11
...
101
102

SOLUTION I USED

select case 
    when number between 0  and 9 then
        LPAD(CAST(number AS CHAR CHARSET UTF8), 2, '0')
    else
        CAST(number AS CHAR CHARSET UTF8)
    end as newNum;

This gets me what I'm looking for and is based off of the accepted answer.

0

You can use a case expression:

select case when number between 0 and 9 then '0' else '' end 
    || number as n 
from t;

Note that || is the sql-standard operator for string concatenation. Unless properly configured MySQL will treat || as logical OR. This behavior is determined by the setting PIPES_AS_CONCAT. You can also enable this and other sane settings with ANSI. You can see what settings you have with:

select @@sql_mode;

It can be changed using:

set @@sql_mode=@@sql_mode || ',PIPES_AS_CONCAT';

or by modifying the config file

However this is purely presentation so I think you should do the formatting of the result in the application instead.

  • This is prefect. Thank you. Only a few minor tweaks to get what you had provided to work. – Keith Bux Oct 9 '18 at 19:06
  • ??? The question is tagged as MySQL-specific. In MySQL || operator is logical OR. So the result will be 0 if number=0 and 1 of number!=0. But the answer is accepted. This may mislead other participants who have the same question. – Akina Oct 10 '18 at 5:03
  • @Lennart Can you show this "sane setting" which will force MySQL to use || operator as concatenation one? – Akina Oct 10 '18 at 5:07
0

Declaring this way:

number INT(5) ZEROFILL

will pad number to at least 5 digits with leading zeros. This is the only use for (5).

Example:

Showing that zeros are displayed:

CREATE TABLE `zips` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `zip` mediumint(5) unsigned zerofill NOT NULL,
  `state` char(2) DEFAULT NULL,
...
  PRIMARY KEY (`id`),
  KEY `zip` (`zip`),
  KEY `state` (`state`,`county`,`city`)
) ENGINE=InnoDB AUTO_INCREMENT=79992 DEFAULT CHARSET=latin1 ;

mysql> SELECT zip, CONCAT('A-', zip), city
             FROM zips  WHERE zip < 11000  LIMIT 5;
+-------+-------------------+----------------------+
| zip   | CONCAT('A-', zip) | city                 |
+-------+-------------------+----------------------+
| 00501 | A-00501           | HOLTSVILLE           |
| 00501 | A-00501           | I R S SERVICE CENTER |
| 00544 | A-00544           | HOLTSVILLE           |
| 00544 | A-00544           | IRS SERVICE CENTER   |
| 00601 | A-00601           | ADJUNTAS             |
+-------+-------------------+----------------------+
5 rows in set (0.00 sec)

Showing that a too-big value works 'right':

mysql> INSERT INTO zips (zip) VALUES (123456);
Query OK, 1 row affected, 9 warnings (0.00 sec)
-- (The warnings relate to failing to provide values for other columns.)

mysql> SELECT zip, city  FROM zips  ORDER BY zip DESC  LIMIT 5;
+--------+-----------+
| zip    | city      |
+--------+-----------+
| 123456 |           |
|  99950 | KETCHIKAN |
|  99950 | KASAAN    |
|  99950 | EDNA BAY  |
|  99929 | WRANGELL  |
+--------+-----------+
5 rows in set (0.00 sec)
  • Does the zerofill padding remain if the number is being concatenated? And does int(2) zerofill work for 100? – Keith Bux Oct 25 '18 at 18:12
  • @KeithBuxI added examples to show that it does the 'right' thing for both of your questions. – Rick James Oct 25 '18 at 18:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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