oracle 字符串变量增长,oracle – 将字符串拆分为变量

执行下面的select后,如何将其分配给变量.

如果输入字符串是“x / y / z”,我必须将“x”存储到变量A中,将“y”存储到变量B中,将z存储到变量“C”中.

假设如果string是“x / z”,那么我必须将“x”存储到变量中,然后将z存储到变量“c”中.

在所有其他情况下,假设输入字符串仅为“x”或“x / y / z / z”,则无法存储任何内容

SELECT REGEXP_SUBSTR(<>, '[^/]+', 1, LEVEL)

FROM DUAL

CONNECT BY REGEXP_SUBSTR((<>, '[^/]+', 1, LEVEL)

IS NOT NULL;

最佳答案 我只使用PL / SQL而不是使用SQL,因为似乎不需要引入不必要的上下文切换：

declare

v_a varchar2(10);

v_b varchar2(10);

v_c varchar2(10);

v_string varchar2(33);

procedure split_string (p_string in varchar2,

p_a out varchar2,

p_b out varchar2,

p_c out varchar2)

is

begin

if regexp_count(p_string, '/') = 2 then

p_a := regexp_substr(p_string, '[^/]+', 1, 1);

p_b := regexp_substr(p_string, '[^/]+', 1, 2);

p_c := regexp_substr(p_string, '[^/]+', 1, 3);

elsif regexp_count(p_string, '/') = 1 then

p_a := regexp_substr(p_string, '[^/]+', 1, 1);

p_c := regexp_substr(p_string, '[^/]+', 1, 2);

end if;

end;

begin

v_string := 'x/y/z';

split_string(v_string, v_a, v_b, v_c);

dbms_output.put_line('v_string = "'||v_string||'", v_a = "'||v_a||'", v_b = "'||v_b||'", v_c = "'||v_c||'"');

v_string := 'x/y';

split_string(v_string, v_a, v_b, v_c);

dbms_output.put_line('v_string = "'||v_string||'", v_a = "'||v_a||'", v_b = "'||v_b||'", v_c = "'||v_c||'"');

v_string := 'x/y/z/1';

split_string(v_string, v_a, v_b, v_c);

dbms_output.put_line('v_string = "'||v_string||'", v_a = "'||v_a||'", v_b = "'||v_b||'", v_c = "'||v_c||'"');

v_string := 'x';

split_string(v_string, v_a, v_b, v_c);

dbms_output.put_line('v_string = "'||v_string||'", v_a = "'||v_a||'", v_b = "'||v_b||'", v_c = "'||v_c||'"');

end;

/

v_string = "x/y/z", v_a = "x", v_b = "y", v_c = "z"

v_string = "x/y", v_a = "x", v_b = "", v_c = "y"

v_string = "x/y/z/1", v_a = "", v_b = "", v_c = ""

v_string = "x", v_a = "", v_b = "", v_c = ""

如果你绝对必须使用SQL,则不需要使用connect by – 你可以将结果分成3列,以匹配你想要输入结果的3个变量：

with strings as (select 'x/y/z' str from dual union all

select 'x/y' str from dual union all

select 'x/y/z/1' str from dual union all

select 'x' str from dual)

select str,

case when regexp_count(str, '/') in (1, 2) then regexp_substr(str, '[^/]+', 1, 1) end v_a,

case when regexp_count(str, '/') = 2 then regexp_substr(str, '[^/]+', 1, 2) end v_b,

case when regexp_count(str, '/') = 2 then regexp_substr(str, '[^/]+', 1, 3)

when regexp_count(str, '/') = 1 then regexp_substr(str, '[^/]+', 1, 2)

end v_c

from strings;

STR V_A V_B V_C

------- --------------------- --------------------- ---------------------

x/y/z x y z

x/y x y

x/y/z/1

x