select substr('张三(学)',1,instr('张三(学)','(')-1) from dual --- 张三
--方法1:使用case函数 with test as( select '张三' name from dual union all select '张三(XX)' name from dual union all select '张三(YY)' name from dual) SELECT CASE WHEN instr(NAME, '(') > 0 THEN substr(NAME, 1, instr(NAME, '(') - 1) WHEN instr(NAME, '(') > 0 THEN substr(NAME, 1, instr(NAME, '(') - 1) ELSE NAME END FROM test;
--方法2:使用decode函数 with test as( select '张三' name from dual union all select '张三(XX)' name from dual union all select '张三(YY)' name from dual) SELECT decode(instr(NAME, '('), 0, decode(instr(NAME, '('), 0, NAME, substr(NAME, 1, instr(NAME, '(') - 1)), substr(NAME, 1, instr(NAME, '(') - 1)) FROM test;
这样总行了吧SQL> SQL> with test as( 2 select '张三' name from dual union all 3 select '张三(XX)' name from dual union all 4 select '张三(YY)' name from dual) 5 select REGEXP_REPLACE(name,'(.+)[\(|\(](.+)','\1') from test;REGEXP_REPLACE(NAME,'(.+)[\(|\ -------------------------------------------------------------------------------- 张三 张三 张三SQL>
create or replace function f_con(name in varchar2) return varchar2 is begin if instr(name, '(') > 0 then return substr(name, 0, instr(name, '(') - 1); elsif instr(name, '(') > 0 then return substr(name, 0, instr(name, '(') - 1); else return name; end if; end;
with tt as(select '张三(学) 李四(学) 王五() ' a from dual) select regexp_replace(a,'(\([[:alnum:]]*\))|(([[:alnum:]]*))') from tt;
select substr(name,1, decode(instr(replace(name,'(','('),'('),0,length(name),instr(replace(name,'(','('),'(')-1)) from a; --把最后的a换成你的表名即可
--方法3:学习一下正则表达式 with test as( select '张三' name from dual union all select '张三(XX)' name from dual union all select '张三(YY)' name from dual) select REGEXP_REPLACE(name,'(.+)\((.+)|(.+)((.+)','\1\3') from test;
select substr(name, 1, case when instr(to_single_byte(name),'(') = 0 then length(name) else instr(to_single_byte(name),'(')-1 end) as name from 表名;--测试 SQL> with tt as( 2 select '张三(学)' name from dual union all 3 select '李四(学)' name from dual union all 4 select '王五' name from dual union all 5 select '张额(员)' name from dual 6 ) 7 select 8 substr(name, 9 1, 10 case when instr(to_single_byte(name),'(') = 0 then length(name) 11 else instr(to_single_byte(name),'(')-1 12 end) as name from tt;NAME -------------------- 张三 李四 王五 张额
换一种写法可能更好理解select case when instr(to_single_byte(name),'(') = 0 then name --没有括号取原值 else substr(name,1,instr(to_single_byte(name),'(')-1) --有括号截取到前一位 end as name from 表名;
select substr(name,1,instr(name,'(')-1) from tb Instr取到位置再Substr就OK了
写函数也只是用REF CURSOR返回一条语句的结果集。
---
张三
with test as(
select '张三' name from dual union all
select '张三(XX)' name from dual union all
select '张三(YY)' name from dual)
SELECT CASE
WHEN instr(NAME, '(') > 0 THEN
substr(NAME, 1, instr(NAME, '(') - 1)
WHEN instr(NAME, '(') > 0 THEN
substr(NAME, 1, instr(NAME, '(') - 1)
ELSE
NAME
END
FROM test;
--方法2:使用decode函数
with test as(
select '张三' name from dual union all
select '张三(XX)' name from dual union all
select '张三(YY)' name from dual)
SELECT decode(instr(NAME, '('),
0,
decode(instr(NAME, '('), 0, NAME, substr(NAME, 1, instr(NAME, '(') - 1)),
substr(NAME, 1, instr(NAME, '(') - 1))
FROM test;
SQL> with test as(
2 select '张三' name from dual union all
3 select '张三(XX)' name from dual union all
4 select '张三(YY)' name from dual)
5 select REGEXP_REPLACE(name,'(.+)[\(|\(](.+)','\1') from test;REGEXP_REPLACE(NAME,'(.+)[\(|\
--------------------------------------------------------------------------------
张三
张三
张三SQL>
begin
if instr(name, '(') > 0 then
return substr(name, 0, instr(name, '(') - 1);
elsif instr(name, '(') > 0 then
return substr(name, 0, instr(name, '(') - 1);
else
return name;
end if;
end;
select regexp_replace(a,'(\([[:alnum:]]*\))|(([[:alnum:]]*))') from tt;
decode(instr(replace(name,'(','('),'('),0,length(name),instr(replace(name,'(','('),'(')-1)) from a;
--把最后的a换成你的表名即可
with test as(
select '张三' name from dual union all
select '张三(XX)' name from dual union all
select '张三(YY)' name from dual)
select REGEXP_REPLACE(name,'(.+)\((.+)|(.+)((.+)','\1\3') from test;
select
substr(name,
1,
case when instr(to_single_byte(name),'(') = 0 then length(name)
else instr(to_single_byte(name),'(')-1
end) as name from 表名;--测试
SQL> with tt as(
2 select '张三(学)' name from dual union all
3 select '李四(学)' name from dual union all
4 select '王五' name from dual union all
5 select '张额(员)' name from dual
6 )
7 select
8 substr(name,
9 1,
10 case when instr(to_single_byte(name),'(') = 0 then length(name)
11 else instr(to_single_byte(name),'(')-1
12 end) as name from tt;NAME
--------------------
张三
李四
王五
张额
case when instr(to_single_byte(name),'(') = 0 then name --没有括号取原值
else substr(name,1,instr(to_single_byte(name),'(')-1) --有括号截取到前一位
end as name
from 表名;