const regex = /@Prompt\('[^']*'(\s*,\s*'[^']*')*\)/g;
// Alternative syntax using RegExp constructor
// const regex = new RegExp('@Prompt\\(\'[^\']*\'(\\s*,\\s*\'[^\']*\')*\\)', 'g')
const str = `SELECT
sum(EXT_BAD_DEBT_HIST.BILL_AMT),
sum(EXT_BAD_DEBT_HIST.BILL_OPEN_AMT),
TIME_MONTH.YEAR,
TIME_MONTH.MONTH_NAME,
@Prompt('Desired %','N',,Mono,free,Not_Persistent),
TIME_MONTH.MONTH_NUM_OVERALL,
TIME_MONTH.FIRST_DAY,
TIME_MONTH.LAST_DAY
FROM
TIME_MONTH INNER JOIN EXT_BAD_DEBT_HIST ON (EXT_BAD_DEBT_HIST.MONTH_NUM_OVERALL=TIME_MONTH.MONTH_NUM_OVERALL)
INNER JOIN RSK_SHARED.LO_PRICE_GROUP RSK_SHARED_LO_PRICE_GROUP2 ON (RSK_SHARED_LO_PRICE_GROUP2.PRICE_GROUP_KEY=EXT_BAD_DEBT_HIST.CUST_PGROUP)
WHERE
(
TRUNC(EXT_BAD_DEBT_HIST.BD_DATE) = TRUNC(SYSDATE)
AND
EXT_BAD_DEBT_HIST.CUSTOMER_CATEGORY_CODE = 20
AND
( EXT_BAD_DEBT_HIST.BILL_CYCLE = @Prompt('Bill Cycle','A','MIGRATION\\BC',Mono,primary_key,Not_Persistent) OR -1 = @Prompt('Bill Cycle','A','MIGRATION\\BC',Mono,primary_key,Not_Persistent) )
AND
TIME_MONTH.FIRST_DAY Is Not Null
AND
TIME_MONTH.LAST_DAY Is Not Null
AND
(
( RSK_SHARED_LO_PRICE_GROUP2.PRICE_GROUP In @Prompt('Price Groups Sub-Groups','A','[Lookup] Price Group (2)\\Nested Price Group',Multi,Constrained,Not_Persistent) OR ('All Large 552' In @Prompt('Price Groups Sub-Groups','A','[Lookup] Price Group (2)\\Nested Price Group',Multi,Constrained,Not_Persistent) AND RSK_SHARED_LO_PRICE_GROUP2.PRICE_GROUP_CODE = 4) OR ('All SME' In @Prompt('Price Groups Sub-Groups','A','[Lookup] Price Group (2)\\Nested Price Group',Multi,Constrained,Not_Persistent) AND RSK_SHARED_LO_PRICE_GROUP2.PRICE_GROUP_CODE = 5) OR 'All' In @Prompt('Price Groups Sub-Groups','A','[Lookup] Price Group (2)\\Nested Price Group',Multi,Constrained,Not_Persistent) )
AND
EXT_BAD_DEBT_HIST.CUST_PGROUP <> 154
AND
EXT_BAD_DEBT_HIST.CUST_PGROUP NOT BETWEEN 161 AND 166
)
)
GROUP BY
TIME_MONTH.YEAR,
TIME_MONTH.MONTH_NAME,
@Prompt('Desired %','N',,Mono,free,Not_Persistent),
TIME_MONTH.MONTH_NUM_OVERALL,
TIME_MONTH.FIRST_DAY,
TIME_MONTH.LAST_DAY`;
// Reset `lastIndex` if this regex is defined globally
// regex.lastIndex = 0;
let m;
while ((m = regex.exec(str)) !== null) {
// This is necessary to avoid infinite loops with zero-width matches
if (m.index === regex.lastIndex) {
regex.lastIndex++;
}
// The result can be accessed through the `m`-variable.
m.forEach((match, groupIndex) => {
console.log(`Found match, group ${groupIndex}: ${match}`);
});
}
Please keep in mind that these code samples are automatically generated and are not guaranteed to work. If you find any syntax errors, feel free to submit a bug report. For a full regex reference for JavaScript, please visit: https://developer.mozilla.org/en/docs/Web/JavaScript/Guide/Regular_Expressions