select substr('XXXXABCXXX',instr('XXXXABCXXX','C',1)+1) from dual
select substr('XXXXABCXXX',8) from dual
ORACLE10G,正则表达式SELECT SUBSTR(REGEXP_SUBSTR('XXXXABCXXX','C[^C]*'),2) FROM DUAL
select substr('XXXXABCXXX',instr('XXXXABCXXX','C',1)+1) from dual
SELECT SUBSTR('XXXXABCXXX', INSTR('XXXXABCXXX','C',-1,1)+1) FROM DUAL
SELECT SUBSTR('XXXXABCXXX0', INSTR('XXXXABCXXX','C',-1,1)+1) FROM DUALINSTR(c1, c2, i, j)如果i为负数,那么搜索将从右到左进行
如果只有一个c那非常简单,但是如果有多个c那就要确定c在什么位置。
若一定要是动态的,那写个函数吧: CREATE OR REPLACE FUNCTION get_c(str in nvarchar2, querychar in nvarchar2,num in number) return nvarchar2 as RETURN_str nvarchar2(100); tmp_str nvarchar2(100); ---------------------------------------------------------------------------- /*功能:返回字符中第 NUM 次出现的 querychar 字符后的字符串 */ /*参数:str:需要处理的字符串; querychar:定位字符串; num:第几次出现 */ /*返回:str字符串第num次出现querychar字符串后的字符串 */ ---------------------------------------------------------------------------- begin if str is null or querychar is null or num is null or instr(str, querychar)=0 or num>(length(str)-length(replace(str, querychar, ''))) or num<0 then tmp_str:=null; else tmp_str:=SUBSTR(str, INSTR(str, querychar,1,num)+1); end if;
RETURN_str:=tmp_str; return RETURN_str; end get_c;select get_c('CXXXXACBXXXCC','C', 2) from dual
CREATE OR REPLACE FUNCTION get_c(str in nvarchar2, querychar in nvarchar2,num in number)
return nvarchar2 as RETURN_str nvarchar2(100);
tmp_str nvarchar2(100);
----------------------------------------------------------------------------
/*功能:返回字符中第 NUM 次出现的 querychar 字符后的字符串 */
/*参数:str:需要处理的字符串; querychar:定位字符串; num:第几次出现 */
/*返回:str字符串第num次出现querychar字符串后的字符串 */
----------------------------------------------------------------------------
begin
if str is null or querychar is null or num is null
or instr(str, querychar)=0
or num>(length(str)-length(replace(str, querychar, '')))
or num<0
then
tmp_str:=null;
else
tmp_str:=SUBSTR(str, INSTR(str, querychar,1,num)+1);
end if;
RETURN_str:=tmp_str;
return RETURN_str;
end get_c;select get_c('CXXXXACBXXXCC','C', 2) from dual