Example: Below is the string(sentence) in field and I want to extract the specific data from the below patterns using select query in different fields:

i )

Input : /a03/infor/current/server/infa_sh/ScriptFil/infa_common/adap_main 'FI_RE_PRJ' 'wf_RE_ACC_TIE_HIS_MNT_EN_AM'

Output : Select query to fetch FI_RE_PRJ and wf_RE_ACC_TIE_HIS_MNT_EN_AM

Other pattern I have is :

Input :

$SCRIPTS/run_in.ksh -f FI_FLE_PRJ -wait wf_FI_SV_CNCL_RP_BAS_KF

Output : Select query to fetch FI_FLE_PRJ and wf_FI_SV_CNCL_RP_BAS_KF from input

CodePudding user response：

We can try using REGEXP_SUBSTR with a capture group:

SELECT
    REGEXP_SUBSTR(col, '''(.*?)''', 1, 1, NULL, 1) AS first,
    REGEXP_SUBSTR(col, '''(.*?)''', 1, 2, NULL, 1) AS second
FROM yourTable;

CodePudding user response：

You can use simple string functions (which are much faster than regular expressions):

SELECT SUBSTR( value, pos1   1, pos2 - pos1 - 1 ) AS quote1,
       SUBSTR( value, pos3   1, pos4 - pos3 - 1 ) AS quote2
FROM   (
  SELECT value,
         INSTR(value, '''', 1, 1) AS pos1,
         INSTR(value, '''', 1, 2) AS pos2,
         INSTR(value, '''', 1, 3) AS pos3,
         INSTR(value, '''', 1, 4) AS pos4
  FROM   table_name
)

Which, for the sample data:

CREATE TABLE table_name (value) AS
SELECT '/a03/infor/current/server/infa_sh/ScriptFil/infa_common/adap_main ''FI_RE_PRJ'' ''wf_RE_ACC_TIE_HIS_MNT_EN_AM''' FROM DUAL;

Outputs:

fiddle