Search code examples
stringfunctionplsqlsplittokenize

ORACLE PL-SQL How to SPLIT a string and RETURN the list using a Function

How to Split the given String for the given Delimiter.

Ex:

INPUT

String => '1,2,3,4,5' Delimiter => ','

OUTPUT

1 2 3 4 5

Solution

  • What about this? The regular expression allows for null list elements too.

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

ELEMENT
--------
1
2

4
5

SQL>

    See this post for a function that returns a list element: REGEX to select nth value from a list, allowing for nulls