CREATE OR REPLACE PROCEDURE idsp(item IN VARCHAR2) IS v_bkt_pos NUMBER; v_bce_pos NUMBER; BEGIN SELECT instr(item, '['), instr(item, '{') INTO v_bkt_pos, v_bce_pos FROM dual; IF v_bkt_pos = 0 THEN IF v_bce_pos = 0 THEN dbms_output.put_line(item); ELSE dbms_output.put_line(substr(item, 1, v_bce_pos - 1)); END IF;
ELSE dbms_output.put_line(substr(item, 1, v_bkt_pos - 1)); END IF; END;自己转成function吧。
第一个else里面掉了对大括号的判断,需要加上,。
case when 规格 is not null and 型号 is not null then substr(col,0,instr(col,'[')-1) when 规格 is null then substr(col,0,instr(col,'{')-1) when 型号 is null then substr(col,0,instr(col,'[')-1) else col end
with tmp as ( select '名称[规格]{型号}' as str from dual union all select '名称[规格]' from dual union all select '名称{型号}' from dual union all select '名称' from dual) select substr(str,1,case when instr(str,'[') > 0 then instr(str,'[') - 1 when instr(str,'{') > 0 then instr(str,'{') - 1 else length(str) end ) as a from tmp
with t as (select 's2s[aa]{xx}' str from dual union all select 's..s2s[aa]' str from dual union all select 's2s{xx}' str from dual union all select 'xxs2?s' str from dual) select substr(str, 1, decode(regexp_instr(str, '[\[|\{]'), 0, length(str), regexp_instr(str, '[\[|\{]') - 1)) from t;
问题在具体点可以么
v_bkt_pos NUMBER;
v_bce_pos NUMBER;
BEGIN
SELECT instr(item, '['), instr(item, '{') INTO v_bkt_pos, v_bce_pos FROM dual;
IF v_bkt_pos = 0
THEN
IF v_bce_pos = 0
THEN
dbms_output.put_line(item);
ELSE
dbms_output.put_line(substr(item, 1, v_bce_pos - 1));
END IF;
ELSE
dbms_output.put_line(substr(item, 1, v_bkt_pos - 1));
END IF;
END;自己转成function吧。
when 规格 is null then substr(col,0,instr(col,'{')-1)
when 型号 is null then substr(col,0,instr(col,'[')-1)
else col
end
DECODE(
instr(item, '[')
, 0
, (
DECODE(
instr(item, '{')
, 0
, item
, substr(item, 0, instr(item, '{') - 1)
)
)
, substr(item, 0, instr(item, '[') - 1)
)
from
table
( select '名称[规格]{型号}' as str from dual
union all select '名称[规格]' from dual
union all select '名称{型号}' from dual
union all select '名称' from dual)
select substr(str,1,case when instr(str,'[') > 0 then instr(str,'[') - 1
when instr(str,'{') > 0 then instr(str,'{') - 1
else length(str) end ) as a
from tmp
(select 's2s[aa]{xx}' str
from dual
union all
select 's..s2s[aa]' str
from dual
union all
select 's2s{xx}' str
from dual
union all
select 'xxs2?s' str
from dual)
select substr(str,
1,
decode(regexp_instr(str, '[\[|\{]'),
0,
length(str),
regexp_instr(str, '[\[|\{]') - 1))
from t;