Split the string into variables

Question

After executing below select , How to assign it into variables.

If input string is "x/y/z" , I have to store "x" into variable say A ,"y" into variable B and z into variable "C".

Suppose if string is "x/z" then I have to store "x" into variable say A but z into variable "c".

in all other cases suppose if input string is only "x" or "x/y/z/z" then nothing can be stored

 SELECT REGEXP_SUBSTR(<<Input String>>, '[^/]+', 1, LEVEL)
                               FROM DUAL
                    CONNECT BY REGEXP_SUBSTR((<<Input String>>,  '[^/]+', 1, LEVEL)

 IS NOT NULL;

The question is pretty unclear. For accessing the elements of the query you should read the cursor, it can be done in couple of ways. For example you could iterate elements in `FOR` loop and assing values to variables. — Ilia Maskov, Sep 04 '15 at 11:42

score 3 · Answer 1 · answered Sep 04 '15 at 11:53

Instead of using SQL, I would just use PL/SQL, since there seems no need to introduce an unnecessary context switch:

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 = ""

If you absolutely must use SQL, there is no need to use the connect by - you can just separate the results into 3 columns to match the 3 variables you want to input the results into:

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

score 2 · Answer 2 · edited May 23 '17 at 12:14

Be careful, regex_substr using the format '[^/]+' to parse the string elements do not handle null list elements. Here's a way to extract a specific element from a list that handles nulls, which can be put into a function for reuse (this gets the first element where the separator is a slash):

REGEXP_SUBSTR(string_in, '(.*?)(/|$)', 1, 1, NULL, 1);

See the link above, but call like this simple example where the list elements are extracted in order by a function called get_list_element and assigned to variables. Perhaps you can apply this logic to your needs:

SQL> declare
  2  a varchar2(1);
  3  b varchar2(1);
  4  c varchar2(1);
  5  begin
  6    select get_list_element('x/y/z', 1, '/'),
  7           get_list_element('x/y/z', 2, '/'),
  8           get_list_element('x/y/z', 3, '/')
  9    into a, b, c
 10    from dual;
 11
 12    dbms_output.put_line('a: ' || a);
 13    dbms_output.put_line('b: ' || b);
 14    dbms_output.put_line('c: ' || c);
 15  end;
 16  /
a: x
b: y
c: z

PL/SQL procedure successfully completed.

SQL>

Or fix your original try at turning your elements into rows with this:

SQL> with tbl(str) as (
  2    select 'x/y/z' from dual
  3  )
  4  select regexp_substr(str, '(.*?)(/|$)', 1, level, null, 1) element
  5  from tbl
  6  connect by level <= regexp_count(str, '/')+1;

ELEME
-----
x
y
z

SQL>

Split the string into variables

2 Answers2