统计ORACLE中带字母的记录

1、统计不为纯数字记录

select * from xxx cm where not regexp_like(cm.comm_addr1,'^[[:digit:]]+$')

2、统计带字母记录

select *

  from xxx a

 where length(trim(translate(a.comm_addr1,translate(a.comm_addr1,                         'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',' '),' '))) > 0

3、统计存在空格符号的记录

select *

    from dual a

  where dummy like '% %'

4、统计存在长度大于10的记录

select *

    from dual a

  where length( dummy )>10

5、统计已123开头的记录-1

select *

    from dual a

  where substr( dummy,1,3 )='123'

6、统计已123开头的记录-2

select *

    from dual a

  where dummy like '123%'

7、统计重复记录

select sysdate, count(1)

    from dual a

   group by sysdate

  having count(1) > 1