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
