Search code examples
mysqlsqlregexdatabasenormalization

Finding Partial Duplicates in MySQL (Keys with last 6 characters the same)

Got stuck on this one, know it should be simple.

But I have a list of unique IDs that looks like AB123456, XY584234, CDE987654. The last six characters mean something, so I need to find all rows that have the same last six characters as another (substring).

So ABCD1234 would match XYCD1234, and return the both of them. Need to run this on the whole database and get all the matches, preferably with the matches next to each other.

Is that possible?

Solution

  • You can do this with group by and right. The following returns a list of all ids that look similar:

    select right(id, 6), group_concat(id)
from table t
group by right(id, 6);

    You might want to add:

    having count(*) > 1

    If you don't want singletons.