Need to match and group the same IDs generated from different machine, the query below rocks. Here i have removed leading zero using patindex
select count(test) as cnt,
substring(test, patindex('%[^0]%',test),len(test)) from (
select ('200003') as test
union
select '000200003' as test
union
select ('2A0003') as test
union
select ('002A0003') as test
)ty
group by substring(test, patindex('%[^0]%',test),len(test))