Mysql - using REVERSE LEFT GROUP and GROUP CONCAT to find string that match except for proceeding characters

This was the problem I was trying to solve.
MySql search to find IDs that match but some the Proceed with a Zero

This is a pretty niche little issue eh!


In one of our Software applications we have users with Payroll IDs, but some had been imported with Zero's at the beginning and some without.   Probably due to some excel settings !

What I wanted to do was write a query for this to find these issues.


To solve this I used this logic

1.    I would REVERSE the Payroll Id's
2.   That way I could match the first 6 digits on the LEFT before the we'd get to a zero !
3.   GROUP those that matched
4.  COUNT them and put the highest amounts to the top
5.  Show all of the Payroll Ids so that I could scan the list easily to see which ones could be an issue.



Here's my query.



SELECT count(*), GROUP_CONCAT(identifier) Identifiers FROM eck_payroll as p
WHERE p.identifier != 0  AND p.`state` = 1
GROUP BY LEFT(REVERSE(p.identifier),6)
ORDER BY count(*) DESC;