CODEWITHSUNDEEP
mysql
Steve
Steve

Reputation: 3095

mysql removing leading zeros from field

I have an existing companies table with unique IDs provided by a 3rd party. The table has grown pretty large (over 100,000 companies) over the last year and we're running into an issue importing additional companies from the supplied data file.

Each company ID is up to 9 characters. The problem is that sometimes they add leading zeros, and sometimes they don't. So 00321390 is the same as 321390.

When we go to import a new data set we look for existing companies with something like this:

WHERE TRIM(LEADING '0' FROM co_grp) = TRIM(LEADING '0' FROM import_grp)

co_grp in the companies table is indexed unique. import_grp from the temporary import table is indexed normal (there could be duplicates in the import file that are handled elsewhere in our import queries.

The problem is that it's taking a substantial amount of time to run this import on this step. I thought about adding a column and duplicating so that we'd have one column with no leading zeros and the other with the two leading zeros, but that doesn't sound like a proper way to handle this.

What would be a better way to go about this? I don't want to do a mass update and remove or add leading zeros because a company that accesses this system likes the leading zeros so they know if it came from an imported file or (no leading zeros) came from another source. Too late to change that now without lots of other changes to the back end.

Upvotes: 3

Views: 5987

Answers (2)

Axel Osorio
Axel Osorio

Reputation: 39

Thank you, it saved me a lot of time.

I was trying to look in a column where string column values was filled with zeros to the left when was created, for example my idclient values that I was looking for was '21050' and in the column values were saved as '00021050' or '021050', etc .

Then my query was:

select importe 
from orders 
where TRIM(LEADING '0' FROM idclient) like '21050';

If you're working in a stored procedure maybe you have a param called clienteIDParam

select importe 
from orders 
where TRIM(LEADING '0' FROM idclient) like clienteIDParam;

This query returns values where idclient is '021050' or '0000021050' or '0021050' In my case all the rows are for the same client with client code 21050.

Upvotes: 1

neerajUdai
neerajUdai

Reputation: 125

In MySql index works from left to right. So if you are trimming anything from left then there won't be any effect of indexing.

Use this :-

WHERE co_grp like '0%' and import_grp like '0%' 
and TRIM(LEADING '0' FROM co_grp) = TRIM(LEADING '0' FROM import_grp)

This way you will already be filtering out non leading zero entries and your query will become more efficient.

Upvotes: 1

Related Questions