I have data in my table, in below format

I want to fetch only sub-strings with format STKFLOW and form data as in table below.

I am using Oracle DB. Please let me know way that will fetch data quickly using query as I have huge data load too.

CodePudding user response：

Here's one option:

Sample data:

SQL> with test (data1, id) as
  2    (select 'STKFLOW03 & STKFLOW04'     , 90 from dual union all
  3     select 'STKFLOW06 This is 6th flow', 89 from dual union all
  4     select 'STKFLOW08,STKFLOW09'       , 91 from dual
  5    )

Query:

  6  select regexp_substr(data1, 'STKFLOW\d ', 1, column_value) data1, id
  7  from test cross join
  8    table(cast(multiset(select level from dual
  9                        connect by level <= regexp_count(data1, 'STKFLOW')
 10                       ) as sys.odcinumberlist));
Enter value for stkflow04:

DATA1                                         ID
------------------------------------- ----------
STKFLOW03                                     90
STKFLOW06                                     89
STKFLOW08                                     91
STKFLOW09                                     91

SQL>

(...) way that will fetch data quickly as I have huge data load

Well, "huge data load" vs. regular expressions isn't very promising. Your best option is to normalize data and keep STKFLOWxx info in a separate column.

CodePudding user response：

You can do it with simple string function and a recursive query (rather than slow regular expressions):

WITH match_positions (data1, id, match_start) AS (
  SELECT data1,
         id,
         INSTR(data1, 'STKFLOW', 1)
  FROM   table_name
UNION ALL
  SELECT data1,
         id,
         INSTR(data1, 'STKFLOW', match_start   LENGTH('STKFLOW00')   1)
  FROM   match_positions
  WHERE  match_start > 0
)
SEARCH DEPTH FIRST BY data1 SET data1_order
SELECT SUBSTR(data1, match_start, LENGTH('STKFLOW00')) AS data1,
       id
FROM   match_positions
WHERE  match_start > 0

Which, for the sample data:

CREATE TABLE table_name (Data1, ID) AS
SELECT 'STKFLOW03 & STKFLOW04',      90 FROM DUAL UNION ALL
SELECT 'STKFLOW06 This is 6th flow', 89 FROM DUAL UNION ALL
SELECT 'STKFLOW08,STKFLOW09',        91 FROM DUAL;

Outputs:

DATA1	ID
STKFLOW03	90
STKFLOW04	90
STKFLOW06	89
STKFLOW08	91
STKFLOW09	91

db<>fiddle here