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})', ''),