sql - 在sql server中截断前导零

Question

我需要代表以下记录

DATA
000200AA
00000200AA
000020BCD
00000020BCD
000020ABC

作为

DATA    CNT
200AA   1
20BCD   2
20ABC   2

有什么想法吗？

Answer 1

使用 patindex

select count(test) as cnt,
 substring(test, patindex('%[^0]%',test),len(test)) from (

  select ('000200AA') as test
  union
  select '00000200AA' as test
  union
  select ('000020BCD') as test
  union
  select ('00000020BCD') as test
  union
  select ('000020ABC') as test

  )ty
 group by substring(test, patindex('%[^0]%',test),len(test))