Plsql best way to split string

Question

What is the best way to extract words from a string? I already made something up because I found many ways and none of them looked 'simple'.

Let's assume there is a procedure called 'change_opening_hours'. This procedure has a time-range string input called 'v_perioden'.

This string looks like:

'10:00-12:00' OR

'10:00-12:00 14:00-16:00' OR

'10:00-12:00 14:00-16:00 18:00-22:00' etc

Now I already made something up myself to exstract every period of time from this input.

  v_perioden   VARCHAR2(50) := '10:00-12:00 14:00-18:00 22:00-24:00';
  ...
  -- loop though time-periode depeningd
  -- on amount of spaces
  FOR i IN 0..REGEXP_COUNT(v_perioden, ' ') LOOP
    -- first period
    IF i = 0 THEN DBMS_OUTPUT.PUT_LINE(SUBSTR(v_perioden, 0, 11));
    -- second period
    ELSIF i = 1 THEN DBMS_OUTPUT.PUT_LINE(SUBSTR(v_perioden, 13, 11));
    --thirt period
    ELSIF i = 2 THEN DBMS_OUTPUT.PUT_LINE(SUBSTR(v_perioden, 25, 11));
    END IF;
  END LOOP;

Output:

10:00-12:00
14:00-18:00
22:00-24:00

Now this way is working fine, but it isn't that capable. I tried to find out how to extract words from a string on a space but this wasn't working out tho.

score 6 · Answer 1 · edited May 23 '17 at 11:54

This method handles NULL elements (try with this string '10:00-12:00 18:00-22:00' to test, handles variable numbers of characters between occurrences of the delimiter without having to be edited, and most important handles a variable number of elements in the list:

SQL> with tbl(v_perioden) as (
      select '10:00-12:00 14:00-16:00 18:00-22:00'  from dual
    )
    select level nbr, regexp_substr(v_perioden, '(.*?)( |$)', 1, level, null, 1) element
    from tbl
    connect by level <= regexp_count(v_perioden, ' ')+1
    order by level;

       NBR ELEMENT
---------- -----------------------------------
         1 10:00-12:00
         2 14:00-16:00
         3 18:00-22:00

SQL>

Here's why you want to make sure you handle NULL list elements: Split comma separated values to columns in Oracle

Always expect the unexpected!

mahi_0707 · Accepted Answer · 2015-12-13T20:10:58.793

2

you should use a combination of SUBSTR and INSTR Function

select  substr('A B C',0,instr('A B C',' ')) from dual  -- A
UNION ALL
select substr( 'A B C',instr('A B C',' ') , instr('A B C',' ',1,2)-1  ) from dual --B
UNION ALL
select substr( 'A B C', instr('A B C',' ',1,2) , instr('A B C',' ') ) from dual  -- C

*Replace A B C with your string

edited Dec 13 '15 at 20:10

answered Dec 10 '15 at 19:00

mahi_0707

1,030
9
17

score 0 · Answer 3 · answered Apr 13 '17 at 07:11

If you ask for a plsql way to do so, you'd probably like to have your own built-in developed in your local toolbox. Here is what you could use: create a function that will return a collection from a separated (any separator) input string:

CREATE OR REPLACE TYPE t_list AS TABLE OF VARCHAR2(100);
CREATE OR REPLACE
FUNCTION seplist_to_coll(p_sep in Varchar2, p_list IN VARCHAR2)
  RETURN t_list
AS
  l_str     VARCHAR2(32767) := p_list || p_sep ;
  l_sep_idx PLS_INTEGER                        ;
  l_idx     PLS_INTEGER     := 1               ;
  l_tab     t_list          := t_list()        ;
BEGIN
  LOOP
    l_sep_idx := INSTR(l_str, p_sep, l_idx);
    EXIT
  WHEN l_sep_idx = 0;
    l_tab.EXTEND;
    l_tab(l_tab.COUNT) := TRIM(SUBSTR(l_str, l_idx, l_sep_idx - l_idx));
    l_idx              := l_sep_idx + 1;
  END LOOP;
  RETURN l_tab;
END seplist_to_coll;
/

Then handle your collection...

use it in SQL with the table() function: it makes a table you can use in a IN(..) clause:
```
select * from table(seplist_to_coll(' ', '10:00-12:00 14:00-16:00 18:00-22:00'))
```

COLUMN_VALUE
10:00-12:00
14:00-16:00
18:00-22:00

it can handle empty values

select rownum, '<'||column_value||'>' from table(seplist_to_coll(' ', '  x  s'));

ROWNUM COLUMN_VALUE
1           <>
2           <>
3           <x>
4           <>
5           <s>

Plsql best way to split string

3 Answers3

Linked