Forum Stats

  • 3,838,652 Users
  • 2,262,389 Discussions
  • 7,900,721 Comments

Discussions

Removing any leading 0 from all records in a field

CONCEPT21
CONCEPT21 Member Posts: 72 Red Ribbon
edited May 27, 2017 12:24AM in MySQL Community Space

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. 

Answers

  • Barbara Boehmer
    Barbara Boehmer Member Posts: 4,625 Gold Trophy
    edited May 19, 2017 6:44PM

    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
    CONCEPT21 Member Posts: 72 Red Ribbon
    edited May 20, 2017 4:37AM

    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
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited May 26, 2017 10:40PM

    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
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited May 22, 2017 12:55AM

    ...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
    CONCEPT21 Member Posts: 72 Red Ribbon
    edited May 26, 2017 4:31PM

    Hello Gaz,

    Thanks for your reply again!

    I just thought MySQL was more advanced than MariaDB! 

    Let me try your methods. 

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited May 27, 2017 12:24AM

    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)