oracle查詢筆記

168一路發(fā) 2011-01-05

展開全文

好記性當不得爛筆頭，把工作,、學(xué)習中一點東西作個筆記...

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",--任何數(shù)字
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",--任何字母和數(shù)字
REGEXP_REPLACE('A1234aa我們,adb23','[0-9a-fA-F]', '') "filter number and character" --任何英文字母和數(shù)字
FROM dual;

---過濾查詢(也可插入數(shù)據(jù))主要針對特殊字符

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條數(shù)據(jù)

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)百分比例取數(shù)

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;

---乘法口訣查詢(*轉(zhuǎn))偽列的使用(www.)

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;