Split String by Space and Character as Delimiter in Oracle with Regexp_Substr

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

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE TEST( str ) AS
          SELECT 'Hello world - test-test! - test' FROM DUAL
UNION ALL SELECT 'Hello world2 - test2 - test-test2' FROM DUAL;

Query 1:

SELECT Str,
       COLUMN_VALUE AS Occurrence,
       REGEXP_SUBSTR( str ,'(.*?)([[:space:]]-[[:space:]]|$)', 1, COLUMN_VALUE, NULL, 1 ) AS split_value
FROM   TEST,
       TABLE(
         CAST(
           MULTISET(
             SELECT LEVEL
             FROM   DUAL
             CONNECT BY LEVEL < REGEXP_COUNT( str ,'(.*?)([[:space:]]-[[:space:]]|$)' )
           )
           AS SYS.ODCINUMBERLIST
         )
       )

Results:

|                               STR | OCCURRENCE |  SPLIT_VALUE |
|-----------------------------------|------------|--------------|
|   Hello world - test-test! - test |          1 |  Hello world |
|   Hello world - test-test! - test |          2 |   test-test! |
|   Hello world - test-test! - test |          3 |         test |
| Hello world2 - test2 - test-test2 |          1 | Hello world2 |
| Hello world2 - test2 - test-test2 |          2 |        test2 |
| Hello world2 - test2 - test-test2 |          3 |   test-test2 |

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>

Oracle SQL query: Split string by space and get the nth string

You are pretty close. You just need to search for non-spaces, because that is what you want to return:

select REGEXP_SUBSTR(help, '[^ ]+', 1, 2)
from (select 'hello my name is...' as help from dual);

Oracle sql REGEXP to split a string at certain point so as to maintain the meaning- Suggestions required

This could be a way:

with test(x) as
(
    SELECT 'THIS LONG STRING HAS EIGHT WORDS SEVEN SPACES' FROM DUAL
)
select substr(x, 1, 40-instr(reverse(substr(x, 1, 40)), ' ') ) s1,
       substr(x, 40-instr(reverse(substr(x, 1, 40)), ' ')+2) s2 
from test;

which gives:

S1                                     S2
-------------------------------------- ------
THIS LONG STRING HAS EIGHT WORDS SEVEN SPACES

The idea is to use the reverse to work on the string in reverse order, then look for the last space in the first 40 characters (or the first space in the reverted string) and use this position to split the string.

Oracle: Trying to split a string using REGEXP_SUBSTR, CONNECT BY LEVEL functions and getting duplicates

You need to include the ID in the connect-by clause; but for this to wotk you also need to include a non-deterministic function call; e.g.:

AND PRIOR id = id
AND PRIOR dbms_random.value IS NOT NULL

with qry as (
select 1 as id, '<div>What would you like to do today?<a href="#" data-jump="111">Client Service</a><a href="#" data-jump="112">Customer Service</a><a href="#" data-jump="113">Tecnical Service</a></div>' as html_string from dual
union
select 2 as id, '<div>What would you like to do today?<a href="#" data-jump="111">Client Service</a><a href="#" data-jump="112">Customer Service</a><a href="#" data-jump="113">Tecnical Service</a></div><a href="#" data-jump="114">Other Service</a></div>' as html_string from dual
)
SELECT ID,
REGEXP_SUBSTR(html_string, '<a.*?>(.*?).</a>', 1, LEVEL, NULL, 1) as contents,
REGEXP_SUBSTR(html_string, 'data-jump="(.*?)"', 1, LEVEL, NULL, 1) as data_jump
FROM qry
CONNECT BY LEVEL <= REGEXP_COUNT(html_string, '<a.*?>(.*?).</a>')
AND PRIOR id = id
AND PRIOR dbms_random.value IS NOT NULL

ID	CONTENTS	DATA_JUMP
1	Client Servic	111
1	Customer Servic	112
1	Tecnical Servic	113
2	Client Servic	111
2	Customer Servic	112
2	Tecnical Servic	113
2	Other Servic	114

Oracle SQL Select a Variable and split it by semicolon

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>

Split String by Space and Character as Delimiter in Oracle with Regexp_Substr