Split String by Delimiter Position Using Oracle SQL

Split String by delimiter position using oracle SQL

You want to use regexp_substr() for this. This should work for your example:

select regexp_substr(val, '[^/]+/[^/]+', 1, 1) as part1,
       regexp_substr(val, '[^/]+$', 1, 1) as part2
from (select 'F/P/O' as val from dual) t

Here, by the way, is the SQL Fiddle.

Oops. I missed the part of the question where it says the last delimiter. For that, we can use regex_replace() for the first part:

select regexp_replace(val, '/[^/]+$', '', 1, 1) as part1,
       regexp_substr(val, '[^/]+$', 1, 1) as part2
from (select 'F/P/O' as val from dual) t

And here is this corresponding SQL Fiddle.

Split string by space and space as delimiter in Oracle with regexp_substr

How about a simple INSTR function?

SQL> with string (id, col) as
  2    (select 1, 'FATALITY, BODY SYS, TEAM BUILDING EVENT.THE EMPLOYEE WAS PARTICIPATING IN A TEAM BUILDING EVENT WHEN HE SUFFERED A HEART ATTACK. THE EVENT WAS FATAL.' from dual union all
  3     select 2, 'THERE IS ONLY HEART IN HERE' from dual),
  4  lookup (word) as
  5    (select 'HEART' from dual union all
  6     select 'FATAL' from dual
  7    )
  8  select s.id,
  9    listagg(l.word, ', ') within group (order by l.word) result
 10  from string s join lookup l on instr(s.col, l.word) > 0
 11  group by s.id;

        ID RESULT
---------- ----------------------------------------
         1 FATAL, HEART
         2 HEART

SQL>

How to Split a comma separated string in Oracle

based on https://blogs.oracle.com/aramamoo/how-to-split-comma-separated-string-and-pass-to-in-clause-of-select-statement :

First, we will form a query, that splits this comma separated string and gives the individual strings as rows.

SQL> select regexp_substr('20.4,12.5,3.5,0.2,0.2','[^,]+', 1, level) from dual
     connect by regexp_substr('20.4,12.5,3.5,0.2,0.2', '[^,]+', 1, level) is not null;


REGEXP_SUBSTR('20.4,1
---------------------
20.4                 
12.5                 
3.5                  
0.2                  
0.2  

The above query iterates through the comma separated string, searches for the comma (,) and then splits the string by treating the comma as delimiter. It returns the string as a row, whenever it hits a delimiter.

Oracle SQL splitting a string with repeated delimiters

It doesn't sound like you need any regular expressions if you only want to split on the first /. You can use INSTR to find the position of the first / character easily, then use SUBSTR to split at that position.

WITH test_input AS (
    SELECT 'xxx/xx/x' AS val
    FROM dual
)

SELECT t.val                                AS original_string,
       INSTR(t.val,'/',1)                   AS position_of_first_slash,
       SUBSTR(t.val,0,INSTR(t.val,'/',1)-1) AS substring_before_first_slash,
       SUBSTR(t.val,INSTR(t.val,'/',1)+1)   AS substring_after_first_slash
FROM test_input t

---

Related Topics