REGEXP_SUBSTR - How to return value between ' '?

Question

I need to return values from ' ' from below string (eg. for psOrderByClause I need value xxx_seqno). Additionally I've also need to return TRUE/FALSE values for some variables - there is no single quotes (eg. pbUseDisplayName I need TRUE).

I doesn't has to be in one query, there should be seperate query for every variable (eg. psOrderByClause, psSeparator etc.)

prcCSVGenerator(pnRunKey => pnRunKey, pnConfigKey => pnConfigKey, psOrderByClause => 'xxx_seqno', psSeparator => '|', pbUseDisplayName => TRUE, pbUseWindowsEOL => TRUE);

For example from above string I need to return xxx_seqno in one query and TRUE for another one

There is not always 'spaces' like above, sometimes it looks like "psOrderByClause=> 'xxx_seqno'" or "psOrderByClause=>'xxx_seqno'"

I'm not feeling Regex well, I've only something like this, but this returns all characters after psSeparator

REGEXP_SUBSTR (text,'(psSeparator)(.*)',1,1,'i',2) regex

Can anyone help me with that?

Thanks

Example you posted doesn't match what you explained. I would imagine that you need to get a pipe character for psSeparator (as it is enclosed into single quotes), while you don't need anything for pBUseDisplayName because its TRUE isn't enclosed into single quotes. So, could you rephrase the question and match *image* with *words*? Also, to avoid confusion, say what *exactly* you want as a result. Is it only e.g. xxx_seqno or both psOrderByClause and its value, xxx_seqno. — Littlefoot, Jun 09 '21 at 07:16
Good point, I've updated my question. This should be more clear now — MichalAndrzej, Jun 09 '21 at 07:50

score 2 · Accepted Answer · answered Jun 09 '21 at 08:27

You can use:

SELECT REGEXP_SUBSTR(
         value,
         q'[psOrderByClause\s*=>\s*('(''|[^'])*'|.*?)\s*(,|\))]',
         1,
         1,
         'i',
         1
       ) AS psOrderByClause,
       REGEXP_SUBSTR(
         value,
         q'[psSeparator\s*=>\s*('(''|[^'])*'|.*?)\s*(,|\))]',
         1,
         1,
         'i',
         1
       ) AS psSeparator,
       REGEXP_SUBSTR(
         value,
         q'[pbUseDisplayName\s*=>\s*('(''|[^'])*'|.*?)\s*(,|\))]',
         1,
         1,
         'i',
         1
       ) AS pbUseDisplayName,
       REGEXP_SUBSTR(
         value,
         q'[pbUseWindowsEOL\s*=>\s*('(''|[^'])*'|.*?)\s*(,|\))]',
         1,
         1,
         'i',
         1
       ) AS pbUseWindowsEOL
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name ( value ) AS
SELECT 'prcCSVGenerator(pnRunKey => pnRunKey, pnConfigKey => pnConfigKey, psOrderByClause => ''xxx_seqno'', psSeparator => ''|'', pbUseDisplayName => TRUE, pbUseWindowsEOL => TRUE);' FROM DUAL

Outputs:

PSORDERBYCLAUSE PSSEPARATOR PBUSEDISPLAYNAME PBUSEWINDOWSEOL

'xxx_seqno' '|' TRUE TRUE

If you don't want the surrounding quotes then you can trim them as required.

db<>fiddle here

REGEXP_SUBSTR - How to return value between ' '?

1 Answers1