Oracle SQL Select a Variable and split it by semicolon

June 9, 2022

I am currently trying to split the Value of a Variable to select.

Variable = 'test;test1;test2;test3'

I would like it to look like this when using Select:

test    test1    test2    test3

So the splited Values are in different collumn when selected

Is there a Function to do that or is it even possible to do something like this?

>Solution :

OK, you have semi-colon separated list of values. You said that you want to have them in different rows, but – that’s not what example shows … this:

I would like it to look like this when using Select:

test test1 test2 test3

is only one row, with space as a separator.

Anyway: presuming that you really want different rows, then replace current separator with a line feed character (chr(10)), e.g.

SQL> select replace('test;test1;test2;test3', ';', chr(10)) result
  2  from dual;

RESULT
----------------------
test
test1
test2
test3


SQL>

As it turns out you need different columns after all, then – with such a sample data – regular expressions are a simple solution:

SQL> with test (col) as
  2    (select 'test;test1;test2;test3' from dual)
  3  select regexp_substr(col, '\w+', 1, 1) col1,
  4         regexp_substr(col, '\w+', 1, 2) col2,
  5         regexp_substr(col, '\w+', 1, 3) col3,
  6         regexp_substr(col, '\w+', 1, 4) col4
  7  from test;

COL1 COL2  COL3  COL4
---- ----- ----- -----
test test1 test2 test3

SQL>