CODEWITHSUNDEEP
mysqlsqlregexdatabasenormalization
Bryant
Bryant

Reputation: 454

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?

Upvotes: 0

Views: 301

Answers (2)

Indra Prakash Tiwari
Indra Prakash Tiwari

Reputation: 1057

Please use below query to get your result.

select * from tablename where right(columnname,6)= value

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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.

Upvotes: 2

Related Questions