MySQL number-string formatting to pad zeros on the left of a string with numbers after a slash

Let us first create a table −

mysql> create table DemoTable1369
    -> (
    -> BatchId varchar(20)
    -> );
Query OK, 0 rows affected (0.46 sec)

Insert some records in the table using insert command. We have inserted numbers here separated by a slash −

mysql> insert into DemoTable1369 values('19/5');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable1369 values('19/78');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable1369 values('19/567');
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable1369 values('19/1234');
Query OK, 1 row affected (0.11 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1369;

This will produce the following output −

+---------+
| BatchId |
+---------+
| 19/5    |
| 19/78   |
| 19/567  |
| 19/1234 |
+---------+
4 row>

Here is the query for number-string formatting. We have set zeros after slash to fill the field. The total field width is determined by the highest field value i.e. 4 here for number “1234” −

mysql> select
    -> concat(left(BatchId,3), lpad(substring(BatchId, 4), 4, '0'))
    -> from DemoTable1369;

This will produce the following output −

+--------------------------------------------------------------+
| concat(left(BatchId,3), lpad(substring(BatchId, 4), 4, '0')) |
+--------------------------------------------------------------+
| 19/0005                                                      |
| 19/0078                                                      |
| 19/0567                                                      |
| 19/1234                                                      |
+--------------------------------------------------------------+
4 rows in set (0.00 sec)

AmitDiwan

e

Updated on: 08-Nov-2019

189 Views

Kickstart Your Career

Get certified by completing the course

Get Started