Finding similar patterns in numbers using mysql

Question

I am using mysql. Still learning things about it.

I have a table that has a column called UPC that is char(13)

I am finding that there are records where there are patterns like:

4009426030
04009426030

Having UPCs like those above means I have two records for the same item. I need to find all the records that are the same so I can merge them. So I tried

SELECT * 
  FROM products p1 
 WHERE prod_id in (
     SELECT prod_id
       FROM products p2 
      WHERE lpad(p2.upc, 13, '0') = lpad(p1.upc, 13, '0') 
        AND p2.upc != p1.upc 
)

My idea was that I would find the records where '4009426030' != '04009426030' but '0004009426030' == '0004009426030' so I don't match on the same records, only different ones.

But I get no results! Can someone see what I am doing wrong?

D'Arcy Rittich · Accepted Answer · 2015-10-16 16:24:07Z

1

Try this:

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE products
    (`prod_id` int,`upc` varchar(25))
;

INSERT INTO products
    (`prod_id`, `upc`)
VALUES
    (1, '4009426030'),
    (2, '04009426030')
;

Query 1:

SELECT * 
FROM products p1 
inner join products p2 on lpad(p2.upc, 13, '0') = lpad(p1.upc, 13, '0') 
        AND p2.upc > p1.upc

Results:

| prod_id |         upc | prod_id |        upc |
|---------|-------------|---------|------------|
|       2 | 04009426030 |       1 | 4009426030 |

answered Oct 16, 2015 at 16:24

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Finding similar patterns in numbers using mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related