Removing any leading 0 from all records in a field

CONCEPT21

Dear all MySQL Friends,

It is me again! 

I am facing a problem of sorting out million records of numbers.

Now, I have to remove any leading 0 which appears in a particular field of all records.

So could you suggest any good command to accomplish this task?

It seems that I must use some conditional command such as IF but I am unsure whether there is such thing in MySQL 5.7.18.  I think it is an advanced topic, right?

Looking forward to your helpful opinion. 

Barbara Boehmer

What is the data type of the field?  If it is integer or some such numeric format as it should be, then what you have is a display issue not a data issue.  If your data type is char or some such character data, then you can use TRIM.  Do you want to just remove the leading zeroes in a query or update the table and remove them permanently?  You can use TRIM in a query or an update statement.  Please see the demonstration below that shows the differences in data types and usage of TRIM on a CHAR column in a query and an update statement.

-- creation of table, columns and data and results:

mysql> use mysql;
Database changed
mysql> create table test_tab (col1 char(10), col2 integer);
Query OK, 0 rows affected (0.35 sec)

mysql> insert into test_tab(col1, col2) values ('01', 1);
Query OK, 1 row affected (0.05 sec)

mysql> insert into test_tab(col1, col2) values ('0022', 22);
Query OK, 1 row affected (0.04 sec)

mysql> insert into test_tab(col1, col2) values ('000333', 333);
Query OK, 1 row affected (0.16 sec)

mysql> show columns from test_tab;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| col1  | char(10) | YES  |     | NULL    |       |
| col2  | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select col1, col2 from test_tab;
+--------+------+
| col1   | col2 |
+--------+------+
| 01     |    1 |
| 0022   |   22 |
| 000333 |  333 |
+--------+------+
3 rows in set (0.00 sec)

-- usage of TRIM in a query:

mysql> select trim(leading '0' from col1), col2 from test_tab;
+-----------------------------+------+
| trim(leading '0' from col1) | col2 |
+-----------------------------+------+
| 1                           |    1 |
| 22                          |   22 |
| 333                         |  333 |
+-----------------------------+------+
3 rows in set (0.00 sec)

-- usage of TRIM in an update statement:

mysql> update test_tab set col1=trim(leading '0' from col1);
Query OK, 3 rows affected (0.11 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select col1, col2 from test_tab;
+------+------+
| col1 | col2 |
+------+------+
| 1    |    1 |
| 22   |   22 |
| 333  |  333 |
+------+------+
3 rows in set (0.00 sec)

mysql> drop table test_tab;
Query OK, 0 rows affected (0.17 sec)

MySQL>

CONCEPT21

Hello Barbara,

Thanks for your reply.

The mentioned data type is supposed to be a fax number so it is a large positive integer.  However, some people may fill in a second number by / or - so it makes them harder to deal with.

My method is to find each of their absolute value but the character / makes it fails. 

I will try your method.  Thanks. 

Gaz in Oz

Looks like you need to split the string up. Unless you are using mariaDB then you will need to write your own function to do this (mariaDB has regexp_substr(), mysql does not):

CREATE FUNCTION split_str(   str   VARCHAR(255),   delim VARCHAR(12),   pos   INT) RETURNS VARCHAR(255)   return REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, pos),                     LENGTH(SUBSTRING_INDEX(str, delim, pos -1)) + 1),                   delim, '');

You will need to For example:

mysql> select trim(leading '0' from split_str(replace(x.str, '-', '/'), '/', 1)) faxno    -> from  (select '0123 456 789 - 0123 456 999' str) x;+--------------+| faxno        |+--------------+| 123 456 789  |+--------------+1 row in set (0.00 sec)

NOTE: the "from (...)" on line 2 is just to produce some dummy data separated with a "-"

Gaz in Oz

...and here's a way to get the result in one field, still using the split_str function created previously:

select concat(trim(leading '0' from trim(split_str(replace(x.str, '-', '/'), '/', 1))), ' / ',              trim(leading '0' from trim(split_str(replace(x.str, '-', '/'), '/', 2)))       ) strippedfrom  (select '0123 456 789 - 0123 456 999' str) x;+---------------------------+| stripped                  |+---------------------------+| 123 456 789 / 123 456 999 |+---------------------------+1 row in set (0.00 sec)

All examples will handle "-" and "/" and replace it with a " / "

...so your update statement would look something like this (not tested):

update your_tableset    your_column = concat(trim(leading '0' from trim(split_sty(replace(your_column, '-', '/'), '/', 1))), ' / ',                            trim(leading '0' from trim(split_str(replace(your_column, '-', '/'), '/', 2)))                     ) ;

You would replace your_table, your_column with your table and your column.

CONCEPT21

Hello Gaz,

Thanks for your reply again!

I just thought MySQL was more advanced than MariaDB! 

Let me try your methods. 

Gaz in Oz

Here's a way to do it without the split_str function, it does exactly the same thing:

select concat(trim(leading '0' from trim(                replace(substring(substring_index(replace(x.str, '-', '/'), '/', 1),                           length(substring_index(replace(x.str, '-', '/'), '/', 0)) + 1), '/', ''))),              ' / ',              trim(leading '0' from trim(                replace(substring(substring_index(replace(x.str, '-', '/'), '/', 2),                           length(substring_index(replace(x.str, '-', '/'), '/', 1)) + 1), '/', '')))       ) strippedfrom  (select '0123 456 789 - 0123 456 999' str UNION       select '0888 955 400 / 0888 559 004') x;+---------------------------+| stripped                  |+---------------------------+| 123 456 789 / 123 456 999 || 888 955 400 / 888 559 004 |+---------------------------+2 rows in set (0.00 sec)