$re = '/(ON|JOIN|WHERE|OR|AND)(\s*?.*)(?<func_name>[a-zA-Z]*?\((?<params>[\w\s\.]+)\))/m';
$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))';
preg_match_all($re, $str, $matches, PREG_SET_ORDER, 0);
// Print the entire match result
var_dump($matches);
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 PHP, please visit: http://php.net/manual/en/ref.pcre.php