SUBSTR的用法
SUBSTRB(STR,NUMBER,NUMBER)
select?t.str,substrb(t.str,2,1),substr(t.str,-3,2)?from?T_TEST_REGEXP_150514?t?where?t.id='substr'?
REGEXP_SUBSTR的用法
REGEXP_SUBSTR(SOURCE_STRING,PATTERN[,POSITION[,OCCURRENCE[,MATCH_PARA_METER]]]
select?T.ID,T.STR,
???????REGEXP_SUBSTR(T.STR,?'[^,]+'),
???????REGEXP_SUBSTR(T.STR,?'[^,]+',?1,?1),
???????REGEXP_SUBSTR(T.STR,?'[^,]+',?1,?2),
???????REGEXP_SUBSTR(T.STR,?'[^,]+',?2,?1),
???????REGEXP_SUBSTR(T.STR,?'[^,]+',?5,?1),??
???????T.ROWID
??from?T_TEST_REGEXP_150514?t
?where?t.id?=?'substr'
?
?
select?T.ID,
???????T.STR,
???????REGEXP_SUBSTR(T.STR,'\d'),?
???????REGEXP_SUBSTR(T.STR,'\d',1,1),??
???????REGEXP_SUBSTR(T.STR,'\d',2,1),??
???????REGEXP_SUBSTR(T.STR,'\d',2,2),
???????T.ROWID
??from?T_TEST_REGEXP_150514?t
?where?t.id?=?'substr'
?
?
?
?
INSTR用法
INSTR(STR,V_STR.NUMBER,NUMBER)
select?instr(t.str,'1',2,1),t.str?from?t_test_regexp_150514?t?where?t.id='instr';
select?instr(t.str,'1',-2,1),t.str?from?t_test_regexp_150514?t?where?t.id='instr';
REGEXP_INSTR用法
REGEXP_INSTR(SOURCE_CHAR,PATTERN[,POSITION[,OCCURRENCE[,RETURN_OPTION[,MATCH_PARAMETER]]]])
select?t.str,
???????regexp_instr(t.str,?'\.'),
???????regexp_instr(t.str,?'\.',?1,?2),
???????regexp_instr(t.str,?'\.',?5,?2)
??from?t_test_regexp_150514?t
?where?t.id?=?'instr';
?
?
REPLACE(STR,STR1,STR2)
REGEXP_REPLACE(SOURCE_CHAR,PATTERN[,REPLACE_STRING[,POSITON[,OCCURRENT[,MATCH_PARAMETER]]]])
?
select?t.id,
???????t.str,
???????regexp_replace(t.str,?'020',?'GZ'),
???????regexp_replace(str,?'(\d{3})(\d{3})',?''),
?
参考资料：
http://www.cnblogs.com/Azhu/archive/2012/04/03/2431127.html#undefined
