1, 'aaa_bbb_cc' 2, '_sssdfdfd'select substring(name2,1,instr(name2,'_')-1) from (SELECT substring(name,instr(name,'_')+1) name2 FROM test) test2 -------------------- 'bbb' ''
--修改下,刚才没考虑只有一个‘_’的情况 select decode(instr(name2,'_'),0,name2,substring(name2,1,instr(name2,'_')-1)) from (SELECT substring(name,instr(name,'_')+1) name2 FROM yyq1 y where instr(name,'_') > 0) test where instr(name2,'_') > 0 ----------------------------------- 'bbb' 'sssdfdfd'
--上面的sql把后面的where条件去掉 select decode(instr(name2,'_'),0,name2,substring(name2,1,instr(name2,'_')-1)) from (SELECT substring(name,instr(name,'_')+1) name2 FROM yyq1 y where instr(name,'_') > 0) test--或者用case when来做 select case when instr(name2,'_') = 0 then name2 else substring(name2,1,instr(name2,'_')-1) end from (SELECT substring(name,instr(name,'_')+1) name2 FROM yyq1 y where instr(name,'_') > 0) test--ps:如果有三个‘_’的时候,你打算怎么取值呢????
1 with t as ( 2 select 'abc_bac_123' s from dual 3 union all 4 select 'abc_123' from dual 5 union all 6 select '_123' from dual 7 union all 8 select 'bcd123' from dual 9 ) 10* select regexp_substr(s||chr(10),'_([^_]*)(_|'||chr(10)||')',1,1,'i',1) s from t SQL> /S ------------------------------------------------ bac 123 123
select ltrim(regexp_substr('bb_cc','_[^_]*'),'_') from dual;
#5楼提供的方法适用于 oracle 11g 提供的 regexp_substr 。oracle 10g 的 1 with t as ( 2 select 'abc_bac_123' s from dual 3 union all 4 select 'abc_123' from dual 5 union all 6 select '_123' from dual 7 union all 8 select 'bcd123' from dual 9 ) 10* select trim('_' from regexp_substr(s||chr(10),'_([^_]*)(_|'||chr(10)||')')) s from t
2, '_sssdfdfd'select substring(name2,1,instr(name2,'_')-1) from
(SELECT substring(name,instr(name,'_')+1) name2 FROM test) test2
--------------------
'bbb'
''
select
decode(instr(name2,'_'),0,name2,substring(name2,1,instr(name2,'_')-1)) from
(SELECT substring(name,instr(name,'_')+1) name2 FROM yyq1 y where instr(name,'_') > 0) test
where instr(name2,'_') > 0
-----------------------------------
'bbb'
'sssdfdfd'
--上面的sql把后面的where条件去掉
select
decode(instr(name2,'_'),0,name2,substring(name2,1,instr(name2,'_')-1)) from
(SELECT substring(name,instr(name,'_')+1) name2 FROM yyq1 y where instr(name,'_') > 0) test--或者用case when来做
select
case when instr(name2,'_') = 0 then name2 else substring(name2,1,instr(name2,'_')-1) end from
(SELECT substring(name,instr(name,'_')+1) name2 FROM yyq1 y where instr(name,'_') > 0) test--ps:如果有三个‘_’的时候,你打算怎么取值呢????
1 with t as (
2 select 'abc_bac_123' s from dual
3 union all
4 select 'abc_123' from dual
5 union all
6 select '_123' from dual
7 union all
8 select 'bcd123' from dual
9 )
10* select regexp_substr(s||chr(10),'_([^_]*)(_|'||chr(10)||')',1,1,'i',1) s from t
SQL> /S
------------------------------------------------
bac
123
123
而且这样取出来的值前后都有下划线,还得再处理下...
2 select 'abc_bac_123' s from dual
3 union all
4 select 'abc_123' from dual
5 union all
6 select '_123' from dual
7 union all
8 select 'bcd123' from dual
9 )
10* select trim('_' from regexp_substr(s||chr(10),'_([^_]*)(_|'||chr(10)||')')) s from t