const regex = /(ON|JOIN|WHERE|OR|AND)(\s*?.*)(?<func_name>[a-zA-Z]*?\((?<params>[\w\s\.]+)\))/gm;
// Alternative syntax using RegExp constructor
// const regex = new RegExp('(ON|JOIN|WHERE|OR|AND)(\\s*?.*)(?<func_name>[a-zA-Z]*?\\((?<params>[\\w\\s\\.]+)\\))', 'gm')
const str = `
-- From: https://bertwagner.com/posts/how-to-search-and-destroy-non-sargable-queries-on-your-server/
SELECT Name
FROM dbo.CoffeeInventory
WHERE
CONVERT(CHAR(10),CreateDate,121) = '2017-08-19'
SELECT Name, CreateDate
FROM dbo.CoffeeInventory
WHERE DAY(CreateDate) = 19
We want to make it SARGable by doing this instead:
SELECT Name, CreateDate
FROM dbo.CoffeeInventory
WHERE
CreateDate >= '2017-08-19 00:00:00'
AND CreateDate < '2017-08-20 00:00:00'
---- REAL WORLD
SELECT
a.rowid
, LTRIM(RTRIM(a.ordnum)) AS ordnum
, LTRIM(RTRIM(a.itemnum)) AS itemnum
, LTRIM(RTRIM(a.descript)) AS descript
, a.ordtype
, LTRIM(RTRIM(a.name)) AS name
, LTRIM(RTRIM(a.number)) AS number
, a.qtyord
, a.extension
, a.price
, a.deldate
, a.sfstatus
, LTRIM(RTRIM(a.slpcode)) AS slpcode
, a.grouporder
, a.allocated
, a.allocamt
, ISNULL(LTRIM(RTRIM(a.slpname)), '') AS slpname
, CASE WHEN b.custponum LIKE '%REPL%' OR b.custponum LIKE 'RMA%' THEN 1 ELSE 0 END AS IsReturn
, LTRIM(RTRIM(b.custponum)) AS custponum
, a.alloc_flag
, a.keyid
, d.comment
, f.comment AS custcomment
, b.orddate
, a.etadate
, a.EtaDisplayString
, LTRIM(RTRIM(a.filenum)) AS filenum
, a.credithold_cust
, a.credithold_so
, a.sfstatus_name
, ISNULL(b.ordtot,0) AS ordtot
, b.Comment AS GoldenSoComment
, h.termcode
, h.crlimit
, g.InvoiceTotal -- (h.crlimit - ISNULL(g.InvoiceTotal,0)) AS AvailableCredit
, CASE WHEN
h.crlimit < (h.crlimit - ISNULL(g.InvoiceTotal,0))
THEN h.crlimit
ELSE (h.crlimit - ISNULL(g.InvoiceTotal,0))
END AS AvailableCredit
, LTRIM(RTRIM(b.repcode)) AS repcode
, a.shipgroup
, 'readytoship' = CASE WHEN
b.ordtype IN ('S', 'B')
AND (
-- Credit Card On File
b.termcode IN ('CC')
-- Has Terms and Available Credit
OR CHARINDEX(LEFT(b.termcode,1),'1234567890') > 1
)
AND b.credithold = 0
AND h.credithold = 0
AND i.whcode IN ('A')
AND b.deldate <= GETDATE()
AND CHARINDEX('RESERVE',UPPER(b.custponum)) = 0
AND a.alloc_flag = 2
AND a.shipgroup = 0
THEN 1 ELSE 0 END
, CASE
WHEN b.paymethod = 'P' THEN 'PREPAID'
WHEN b.paymethod = 'U' THEN 'PICKUP'
WHEN b.paymethod = 'C' THEN 'COLLECT'
WHEN b.paymethod = 'T' THEN 'THIRD PARTY'
ELSE ''
END AS paymethod
, CASE
WHEN b.paymethod = 'P' THEN
CASE
WHEN b.fslid = 1 THEN 'STANDARD'
WHEN b.fslid = 2 THEN 'EXPEDITED'
WHEN b.fslid = 3 THEN 'RUSH'
ELSE ''
END
ELSE ''
END AS fsl
, b.credcard
, b.onetimecard
FROM RSRPT_ItemAllocation a
-- LEFT JOIN ODS.dbo.vwSOORDEHT b ON LTRIM(RTRIM(a.ordnum)) = LTRIM(RTRIM(b.ordnum))
LEFT JOIN data_SODetailProperties d ON a.keyid = d.keyid
LEFT JOIN data_CustomerProperties f ON a.number = f.number
LEFT JOIN (
SELECT
[cust no] AS number
, SUM([Amount Open]) AS InvoiceTotal
FROM pivot_ARAging
GROUP BY [cust no]
) g ON a.number = g.number
INNER JOIN ODS..vwarcustmm h ON a.number = h.number
LEFT JOIN ODS..soordedt i
ON a.ordnum = LTRIM(RTRIM(i.ordnum)) AND a.keyid = LTRIM(RTRIM(i.keyid))
-- short query
-- Newlined
select RTRIM(LTRIM(ordnum))
as ordnum_trimmed from ODS.dbo.vwSOORDEHT
where ordnum = LTRIM(RTRIM(ordnum))
-- Inlined
select RTRIM(LTRIM(ordnum)) as ordnum_trimmed from ODS.dbo.vwSOORDEHT where ordnum = LTRIM(RTRIM(@ordnum))`;
// 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