记录一下,总感觉会用到,终于可以关电脑了
1.oracle学习中,经常会用到dual这张表,那么你试过select * from dual;???
语句
SELECT * FROM dual;
结果:
DUMMY
X
dummy:虚拟的,假的。
2. 要点:伪列 level,lpad 填充函数,connect by 子句
lpad( string, padded_length, [ pad_string ] )
lpad函数是Oracle数据库函数,lpad函数从左边对字符串使用指定的字符进行填充。从其字面意思也可以理解,l是left的简写,pad是填充的意思,所以lpad就是从左边填充的意思。
with x as
( select 'aa' chr from dual
union all
select 'bb' chr from dual)
select level ,chr,lpad( ' ' ,( LEVEL)* 5 , '-' )||chr other from x connect by level <= 2;
结果:
1 aa ---- aa
2 aa --------- aa
2 bb --------- bb
1 bb ---- bb
2 aa --------- aa
2 bb --------- bb
3.要点:connect by 子句,伪列 level,rownum
方法一
SELECT '爱我中国'||LEVEL||'-123456' FROM dual CONNECT BY LEVEL <=12;
结果:
爱我中国1-123456
爱我中国2-123456
爱我中国3-123456
爱我中国4-123456
爱我中国5-123456
爱我中国6-123456
爱我中国7-123456
爱我中国8-123456
爱我中国9-123456
爱我中国10-123456
爱我中国11-123456
爱我中国12-123456
方法二
SELECT '爱我中国'||ROWNUM||'-123456' FROM dual CONNECT BY ROWNUM <=12;
结果:
爱我中国1-123456
爱我中国2-123456
爱我中国3-123456
爱我中国4-123456
爱我中国5-123456
爱我中国6-123456
爱我中国7-123456
爱我中国8-123456
爱我中国9-123456
爱我中国10-123456
爱我中国11-123456
爱我中国12-123456
4.regexp用法练习
regexp用法学习
-- regexp_replace
SELECT * FROM tmp;
SELECT t.STR,
REGEXP_replace(t.STR,'020','gz'),
REGEXP_replace(t.STR,'(\d{3})(\d{3})','<\2\1>',3)
FROM tmp t
WHERE t.ID = 'replace';
-- regexp_like 只能用于条件表达式
-- 查询id为like,同时字符串中包含以a|A开头,后面为数字的字符串
SELECT str FROM tmp t WHERE t.ID = 'like' AND regexp_like(t.STR,'a\d+','i');
-- 查询id为like,同时字符串开头为a|A,后面为数字的字符串
SELECT str FROM tmp t WHERE t.ID = 'like' AND regexp_like(t.STR,'^a\d+','i');
--
SELECT str FROM tmp t WHERE t.ID = 'like' AND regexp_like(t.STR,'^a\d+$','i');
-- ERGEXP_substr 字符串的截取
-- regexp_substr(source_char,pattern,position,occurrence)
-- regexp_substr(输入的字符串,正则表达式,标识从第几个字符开始匹配正则表达式,标识第几个匹配项)
SELECT t.STR,
REGEXP_substr(t.STR,'[^,]+') str,
regexp_substr(t.STR,'[^,]+',1,1) str,
regexp_substr(t.STR,'[^,]+',1,2) str,
regexp_substr(t.STR,'[^,]+',2,1) str,
regexp_substr(t.STR,'[^,]+',2,2) str
FROM tmp t WHERE t.id = 'substr'; o
SELECT regexp_substr('123456789','\d',1,LEVEL) FROM dual CONNECT BY LEVEL <=9;
-- regexp_instr
-- regexp_instr(source_char,pattern,position,occurrence,return_option,match_parameter)
-- regexp_instr(输入的字符串,正则表达式,标识从第几个 字符 开始匹配正则表达式,标识第几个匹配项,返回项,匹配选项)
SELECT t.STR,
regexp_instr(t.STR,'\.') ind,
regexp_instr(t.STR,'\.',1,1) ind,
REGEXP_instr(t.STR,'\.',1,2) ind,
regexp_instr(t.STR,'\.',2,2) ind,
regexp_instr(t.STR,'\.',5,2) ind
FROM tmp t WHERE t.ID = 'instr';
------------------
select
regexp_instr('192.168.0.1','\.',1,level) ind , -- 点号. 所在的位置
regexp_instr('192.168.0.1','\d',1,level) ind -- 每个数字的位置
from dual
connect by level <= 9
-----------------
SELECT * FROM tmp t WHERE t.ID = 'instr';
-----------------
SELECT /*t.STR,
regexp_instr(t.STR,'\.',1,LEVEL) ind,
regexp_instr(t.STR,'\d',1,LEVEL) ind,*/
regexp_instr('192.168.0.1','\.',1,LEVEL) ind,
regexp_instr('192.168.0.1','\d',1,LEVEL) ind,
regexp_instr('192.168.0.1','\.',1,level) ind , -- 点号. 所在的位置
regexp_instr('192.168.0.1','\d',1,level) ind, -- 每个数字的位置
LEVEL ROWSnum
FROM dual connect by level <= 9 ;
--------------------
--一个有趣的玩具
--------------------
with sudoku as (
select '020000080568179234090000010030040050040205090070080040050000060289634175010000020' as line
from dual
),
tmp as (
select regexp_substr(line,'\d{9}',1,level) row_line,
level col
from sudoku
connect by level<=9
)
select regexp_replace( row_line ,'(\d)(\d)(\d)(\d)(\d)(\d)(\d)(\d)(\d)','\1 \2 \3 \4 \5 \6 \7 \8 \9') row_line
from tmp;
-----------------
select regexp_replace( row_line ,'(\d)(\d)(\d)(\d)(\d)(\d)(\d)(\d)(\d)','\1 \2 \3 \4 \5 \6 \7 \8 \9') row_line
FROM (
select regexp_substr(line,'\d{9}',1,level) row_line,
level col
from (
select '020000080568179234090000010030040050040205090070080040050000060289634175010000020' as line
from dual)
connect by level<=9
)