REGEXP_SUBSTR – How to return value between ‘ ‘?

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

Answer

You can use:

SELECT REGEXP_SUBSTR(
         value,
         q'[psOrderByClauses*=>s*('(''|[^'])*'|.*?)s*(,|))]',
         1,
         1,
         'i',
         1
       ) AS psOrderByClause,
       REGEXP_SUBSTR(
         value,
         q'[psSeparators*=>s*('(''|[^'])*'|.*?)s*(,|))]',
         1,
         1,
         'i',
         1
       ) AS psSeparator,
       REGEXP_SUBSTR(
         value,
         q'[pbUseDisplayNames*=>s*('(''|[^'])*'|.*?)s*(,|))]',
         1,
         1,
         'i',
         1
       ) AS pbUseDisplayName,
       REGEXP_SUBSTR(
         value,
         q'[pbUseWindowsEOLs*=>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