substr和instr的用法
declare
t varchar2(200);
pos integer;
i integer;
num integer := 0;
begin
t := '192.1681.200;10.2.3.12;10.3.123.10;45.6,54.2;32.8,163.5;' ;
select length(t)-length(replace(t,';','')) into num from dual;
pos := 0;
for i in 1..num loop
dbms_output.put_line('count :'||i);
dbms_output.put_line('position :'||instr(t,';',1,i));
if (i-1 = 0) then
dbms_output.put_line('string :'||substr(t,pos+1,instr(t,';',1,i)-1) );
else
dbms_output.put_line('string :'||substr(t,pos+1,instr(t,';',1,i)-instr(t,';',1,i-1)));
end if;
pos := instr(t,';',1,i);
end loop;
end;
/
count :1
position :13
string :192.1681.200
count :2
position :23
string :10.2.3.12;
count :3
position :35
string :10.3.123.10;
count :4
position :45
string :45.6,54.2;
count :5
position :56
string :32.8,163.5;
PL/SQL procedure successfully completed.
====================================================================
Oracle正则表达式函数:regexp_like、regexp_substr、regexp_instr、regexp_replace
regexp_like 只能用于条件表达式,和 like 类似,但是使用的正则表达式进行匹配
regexp_substr 函数,和 substr 类似,用于拾取合符正则表达式描述的字符子串
regexp_instr 函数,和 instr 类似,用于标定符合正则表达式的字符子串的开始位置
regexp_replace 函数,和 replace 类似,用于替换符合正则表达式的字符串
====================================================================================================================
2、REGEXP_LIKE(x,pattern[,match_option])用于在x中查找正则表达式pattern,该函数还可以提供一个可选的参数match_option字符串说明默认的匹配选项。match_option的取值如下:
‘c’ 说明在进行匹配时区分大小写(缺省值);
'i' 说明在进行匹配时不区分大小写;
'n' 允许使用可以匹配任意字符的操作符;
'm' 将x作为一个包含多行的字符串。
sql 代码
declare
v_date date;
begin
select sysdate into v_date from dual where regexp_like(to_char(sysdate,'yyyy'),'^2[0-9]{0,2}1$','n');
dbms_output.put_line('date: '||v_date);
end;
7 /
date: 2011-05-08 18:20:59
PL/SQL procedure successfully completed.
====================================================================================================================
3、REGEXP_INSTR(x,pattern[,start[,occurrence[,return_option[, match_option]]]])用于在x中查找pattern。
返回pattern在x中出现的位置。匹配位置从1开始。可以参考字符串函数 INSTR(),参数相关:
'start' 开始查找的位置;
'occurrence' 说明应该返回第几次出现pattern的位置;
'eturn_option' 说明应该返回什么整数。若该参数为0,则说明要返回的整数是x中的一个字符的位置;
若该参数为非0的整数,则说明要返回的整数为x中出现在pattern之后的字符的位置;
'match_option' 修改默认的匹配设置。
sql 代码
declare
v_result integer;
begin
--找出l开头t结尾的 中间有1至4个任意字母的单词在第一个参数中第一次出现的位置
select regexp_instr('But, soft! What light through yonder window breaks','l[[:alpha:]]{1,4}t',1,1,0,'n') into v_result from dual;
dbms_output.put_line(v_result);
--找出l开头t结尾的 中间有1至3个任意字母的单词在第一个参数中第二次出现的位置
SELECT REGEXP_INSTR('But, soft! What light through lot window','l[[:alpha:]]{1,3}t',1,2,0,'n') INTO V_RESULT
FROM DUAL;
dbms_output.put_line(v_result);
end;
12 /
17
31
PL/SQL procedure successfully completed.
====================================================================================================================
4、REGEXP_REPLACE(x,pattern[,replace_string[,start[,occurrence[, match_option]]]])用于在x中查找pattern,并将其替换为replae_string。可以参考字符串函数 REPLACE(),参数同REGEXP_INSTR函数,参考第3
DECLARE
V_RESULT VARCHAR2(255);
BEGIN
-- 用‘XXX’代替‘light'和‘lot’
SELECT REGEXP_REPLACE('But, soft! What light through lot window breaks','l[[:alpha:]]{1,4}t','XXX',1,1,'n') INTO V_RESULT
FROM DUAL;
DBMS_OUTPUT.PUT_LINE(V_RESULT);
--(默认替换所有)
SELECT REGEXP_REPLACE('But, soft! What light through lot window breaks','l[[:alpha:]]{1,4}t','XXX') INTO V_RESULT
FROM DUAL;
DBMS_OUTPUT.PUT_LINE(V_RESULT);
-- 用‘OOO’只代替第二个符合正则式的‘lot’
SELECT REGEXP_REPLACE('But, soft! What light through lot window breaks','l[[:alpha:]]{0,4}t','OOO',1,2,'n') INTO V_RESULT
FROM DUAL;
DBMS_OUTPUT.PUT_LINE(V_RESULT);
END ;
17 /
But, soft! What XXX through lot window breaks
But, soft! What XXX through XXX window breaks
But, soft! What light through OOO window breaks
PL/SQL procedure successfully completed.
====================================================================================================================
5、REGEXP_SUBSTR(x,pattern[,start[,occurrence[, match_option]]])用于在x中查找pattern并返回。可以参考字符串函数 SUBSTR(),参数同REGEXP_INSTR函数,参考第3
sql 代码
DECLARE
V_RESULT VARCHAR2(255);
BEGIN
--返回第一个
SELECT REGEXP_SUBSTR('But, soft! What light through lot window breaks','l[[:alpha:]]{1,4}t') INTO V_RESULT
FROM DUAL;
DBMS_OUTPUT.PUT_LINE('RETURN 2 : '||V_RESULT);
-- 返回第二个子串
SELECT REGEXP_SUBSTR('But, soft! What light through lot window breaks','l[[:alpha:]]{1,4}t',1,2,'n') INTO V_RESULT
FROM DUAL;
DBMS_OUTPUT.PUT_LINE('RETURN 1: '||V_RESULT);
END ;
/
====================================================================================================================
declare
t varchar2(200);
pos integer;
i integer;
num integer := 0;
begin
t := '192.1681.200;10.2.3.12;10.3.123.10;45.6,54.2;32.8,163.5;' ;
select length(t)-length(replace(t,';','')) into num from dual;
pos := 0;
for i in 1..num loop
dbms_output.put_line('count :'||i);
dbms_output.put_line('position :'||instr(t,';',1,i));
if (i-1 = 0) then
dbms_output.put_line('string :'||substr(t,pos+1,instr(t,';',1,i)-1) );
else
dbms_output.put_line('string :'||substr(t,pos+1,instr(t,';',1,i)-instr(t,';',1,i-1)));
end if;
pos := instr(t,';',1,i);
end loop;
end;
/
count :1
position :13
string :192.1681.200
count :2
position :23
string :10.2.3.12;
count :3
position :35
string :10.3.123.10;
count :4
position :45
string :45.6,54.2;
count :5
position :56
string :32.8,163.5;
PL/SQL procedure successfully completed.
====================================================================
Oracle正则表达式函数:regexp_like、regexp_substr、regexp_instr、regexp_replace
regexp_like 只能用于条件表达式,和 like 类似,但是使用的正则表达式进行匹配
regexp_substr 函数,和 substr 类似,用于拾取合符正则表达式描述的字符子串
regexp_instr 函数,和 instr 类似,用于标定符合正则表达式的字符子串的开始位置
regexp_replace 函数,和 replace 类似,用于替换符合正则表达式的字符串
====================================================================================================================
2、REGEXP_LIKE(x,pattern[,match_option])用于在x中查找正则表达式pattern,该函数还可以提供一个可选的参数match_option字符串说明默认的匹配选项。match_option的取值如下:
‘c’ 说明在进行匹配时区分大小写(缺省值);
'i' 说明在进行匹配时不区分大小写;
'n' 允许使用可以匹配任意字符的操作符;
'm' 将x作为一个包含多行的字符串。
sql 代码
declare
v_date date;
begin
select sysdate into v_date from dual where regexp_like(to_char(sysdate,'yyyy'),'^2[0-9]{0,2}1$','n');
dbms_output.put_line('date: '||v_date);
end;
7 /
date: 2011-05-08 18:20:59
PL/SQL procedure successfully completed.
====================================================================================================================
3、REGEXP_INSTR(x,pattern[,start[,occurrence[,return_option[, match_option]]]])用于在x中查找pattern。
返回pattern在x中出现的位置。匹配位置从1开始。可以参考字符串函数 INSTR(),参数相关:
'start' 开始查找的位置;
'occurrence' 说明应该返回第几次出现pattern的位置;
'eturn_option' 说明应该返回什么整数。若该参数为0,则说明要返回的整数是x中的一个字符的位置;
若该参数为非0的整数,则说明要返回的整数为x中出现在pattern之后的字符的位置;
'match_option' 修改默认的匹配设置。
sql 代码
declare
v_result integer;
begin
--找出l开头t结尾的 中间有1至4个任意字母的单词在第一个参数中第一次出现的位置
select regexp_instr('But, soft! What light through yonder window breaks','l[[:alpha:]]{1,4}t',1,1,0,'n') into v_result from dual;
dbms_output.put_line(v_result);
--找出l开头t结尾的 中间有1至3个任意字母的单词在第一个参数中第二次出现的位置
SELECT REGEXP_INSTR('But, soft! What light through lot window','l[[:alpha:]]{1,3}t',1,2,0,'n') INTO V_RESULT
FROM DUAL;
dbms_output.put_line(v_result);
end;
12 /
17
31
PL/SQL procedure successfully completed.
====================================================================================================================
4、REGEXP_REPLACE(x,pattern[,replace_string[,start[,occurrence[, match_option]]]])用于在x中查找pattern,并将其替换为replae_string。可以参考字符串函数 REPLACE(),参数同REGEXP_INSTR函数,参考第3
DECLARE
V_RESULT VARCHAR2(255);
BEGIN
-- 用‘XXX’代替‘light'和‘lot’
SELECT REGEXP_REPLACE('But, soft! What light through lot window breaks','l[[:alpha:]]{1,4}t','XXX',1,1,'n') INTO V_RESULT
FROM DUAL;
DBMS_OUTPUT.PUT_LINE(V_RESULT);
--(默认替换所有)
SELECT REGEXP_REPLACE('But, soft! What light through lot window breaks','l[[:alpha:]]{1,4}t','XXX') INTO V_RESULT
FROM DUAL;
DBMS_OUTPUT.PUT_LINE(V_RESULT);
-- 用‘OOO’只代替第二个符合正则式的‘lot’
SELECT REGEXP_REPLACE('But, soft! What light through lot window breaks','l[[:alpha:]]{0,4}t','OOO',1,2,'n') INTO V_RESULT
FROM DUAL;
DBMS_OUTPUT.PUT_LINE(V_RESULT);
END ;
17 /
But, soft! What XXX through lot window breaks
But, soft! What XXX through XXX window breaks
But, soft! What light through OOO window breaks
PL/SQL procedure successfully completed.
====================================================================================================================
5、REGEXP_SUBSTR(x,pattern[,start[,occurrence[, match_option]]])用于在x中查找pattern并返回。可以参考字符串函数 SUBSTR(),参数同REGEXP_INSTR函数,参考第3
sql 代码
DECLARE
V_RESULT VARCHAR2(255);
BEGIN
--返回第一个
SELECT REGEXP_SUBSTR('But, soft! What light through lot window breaks','l[[:alpha:]]{1,4}t') INTO V_RESULT
FROM DUAL;
DBMS_OUTPUT.PUT_LINE('RETURN 2 : '||V_RESULT);
-- 返回第二个子串
SELECT REGEXP_SUBSTR('But, soft! What light through lot window breaks','l[[:alpha:]]{1,4}t',1,2,'n') INTO V_RESULT
FROM DUAL;
DBMS_OUTPUT.PUT_LINE('RETURN 1: '||V_RESULT);
END ;
/
====================================================================================================================