oracle查询笔记

好记性当不得烂笔头，把工作、学习中一点东西作个笔记...

Oracle10g(10.2.0)测试通过

--字符过滤1

select translate('12c24b31a2321', '\1234567890', '\') "filter number",      
translate('12c24b31a2321', '\ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', '\') "filter character",     
translate('12c24b31a2321!@#$%我的未来不是梦', '\ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789', '\') "filter number and character"      
from dual; 

 

--字符过滤2

Select REGEXP_REPLACE('A1234aa我们,adb23','[[:digit:]]', '') "filter number",--任何数字      
REGEXP_REPLACE('A1234aa我们,adb23','[[:alpha:]]', '') "filter character",--任何字母      
REGEXP_REPLACE('A1234aa我们,adb23','[[:upper:]]', '') "filter en u character",--任何大写字母      
REGEXP_REPLACE('A1234aa我们,adb23','[[:punct:]]', '') "filter en l character",--任何小写字母      
REGEXP_REPLACE('A1234aa我们,adb23','[[:alnum:]]', '') "filter number and character",--任何字母和数字      
REGEXP_REPLACE('A1234aa我们,adb23','[0-9a-fA-F]', '') "filter number and character" --任何英文字母和数字      
FROM dual;    

 

---过滤查询(也可插入数据)主要针对特殊字符

select * from tables_name where col1 like '%'||chr(37)||'%';--%      
select * from tables_name where col1 like '%'||chr(38)||'%';--&      
select * from tables_name where col1 like '%'||chr(39)||'%';--'      
select * from tables_name where col1 like '%'||chr(95)||'%';--_      
select * from tables_name where col1 like '%^_%' escape '^';--_     

 

---随机取5条数据

select * from (select * from scott.emp order by dbms_random.random) where rownum<6;     
select * from (select * from scott.emp sample(20));--[0.000001,100)百分比例取数 

 

select * from scott.emp order by  sys_guid();  

 

--获得指定年之间的周末日期 当然每一个月就类推了

select * from (select decode(to_char(trunc(sysdate,'yyyy')+level-1,'d'),7, '周六',1,'周日',null) 星期几,     
trunc(sysdate,'yyyy')+level-1 当前日期 from dual connect by 1<=1 and      
level<=add_months(trunc(sysdate,'yyyy'),12)-1- trunc(sysdate,'yyyy')+1 )bbb where bbb.星期几 is not null;      
  
---乘法口诀查询(*转)伪列的使用(www.itpub.net)

select reverse(ltrim((sys_connect_by_path(     
              reverse( rownum|| 'X' || lv || '=' || lpad(rownum * lv, 2,'0')),'   ')     
            ))) "乘法口诀"    
  from (select level lv from dual connect by level < 10)     
 where lv = 1     
connect by prior lv = lv+1;