单个的是这样,select substr(name,instr('name','_',1,1),length(name)) from org 但是俩个的话需要怎么做?
--测试数据 create table test1(name varchar2(100)); insert into test1 select 'dfsdf_名字' from dual union all select '434.名字' from dual; --执行查询 select case when instr(name, '_',1,1) >0 then substr(name,instr( name, '_',1,1)+1,length(name)) else substr(name,instr( name, '.',1,1)+1,length(name)) end name from test1
SELECT CASE WHEN INSTR (col, '.', 1, 1) > 0 THEN SUBSTR (col, INSTR (col, '.') + 1, LENGTH (col)) WHEN INSTR (col, '_', 1, 1) > 0 THEN SUBSTR (col, INSTR (col, '_') + 1, LENGTH (col)) ELSE col END FROM (SELECT 'a.1' col FROM DUAL UNION ALL SELECT 'a.2' col FROM DUAL UNION ALL SELECT 'a.3' col FROM DUAL UNION ALL SELECT 'a_4' col FROM DUAL UNION ALL SELECT 'a_5' col FROM DUAL UNION ALL SELECT 'a_6' col FROM DUAL)
select case when instr(emp.name,'.',1,1)> 0 then substr(emp.name,instr(emp.name,'.',1,1)+1) when instr(emp.name,'_',1,1)> 0 then substr(emp.name,instr(emp.name,'_',1,1)+1) else '' end t_name from emp
select substr(name, instr(name,'_',1,1) + 1, instr(name,'_',1,2) - 1 - instr(name,'_',1,1) ) as a, substr(name, instr(name,'.',1,1) + 1, instr(name,'.',1,2) - 1 - instr(name,'.',1,1) ) as b from org
select substr(name, instr(name, '_',1,1) + 1, case when instr(name,'_',1,2) >0 then instr(name,'_',1,2) - 1 - instr(name,'_',1,1) else length(name) end ) as a, substr(name, instr(name, '.',1,1) + 1, case when instr(name,'.',1,2) >0 then instr(name,'.',1,2) - 1 - instr(name,'.',1,1) else length(name) end ) as b from org对name再处理一下就行了..
但是俩个的话需要怎么做?
create table test1(name varchar2(100));
insert into test1
select 'dfsdf_名字' from dual union all select '434.名字' from dual;
--执行查询
select
case when instr(name, '_',1,1) >0 then substr(name,instr( name, '_',1,1)+1,length(name)) else substr(name,instr( name, '.',1,1)+1,length(name)) end name
from test1
假如的名字里面不出现,434.或者dfsdf_的话,REPLACE也可以。
WHEN INSTR (col, '.', 1, 1) > 0
THEN SUBSTR (col, INSTR (col, '.') + 1, LENGTH (col))
WHEN INSTR (col, '_', 1, 1) > 0
THEN SUBSTR (col, INSTR (col, '_') + 1, LENGTH (col))
ELSE col
END
FROM (SELECT 'a.1' col
FROM DUAL
UNION ALL
SELECT 'a.2' col
FROM DUAL
UNION ALL
SELECT 'a.3' col
FROM DUAL
UNION ALL
SELECT 'a_4' col
FROM DUAL
UNION ALL
SELECT 'a_5' col
FROM DUAL
UNION ALL
SELECT 'a_6' col
FROM DUAL)
case
when instr(emp.name,'.',1,1)> 0
then substr(emp.name,instr(emp.name,'.',1,1)+1)
when instr(emp.name,'_',1,1)> 0
then substr(emp.name,instr(emp.name,'_',1,1)+1)
else ''
end t_name
from emp
substr(name,
instr(name,'_',1,1) + 1,
instr(name,'_',1,2) - 1 - instr(name,'_',1,1)
) as a,
substr(name,
instr(name,'.',1,1) + 1,
instr(name,'.',1,2) - 1 - instr(name,'.',1,1)
) as b
from org
substr(name,
instr(name, '_',1,1) + 1,
case
when instr(name,'_',1,2) >0
then instr(name,'_',1,2) - 1 - instr(name,'_',1,1)
else length(name)
end
) as a,
substr(name,
instr(name, '.',1,1) + 1,
case
when instr(name,'.',1,2) >0
then instr(name,'.',1,2) - 1 - instr(name,'.',1,1)
else length(name)
end
) as b
from org对name再处理一下就行了..