import Foundation
let pattern = #"(ON|JOIN|WHERE|OR|AND)(\s*?.*)(?<func_name>[a-zA-Z]*?\((?<params>[\w\s\.]+)\))"#
let regex = try! NSRegularExpression(pattern: pattern, options: .anchorsMatchLines)
let testString = #"""
-- 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))
"""#
let stringRange = NSRange(location: 0, length: testString.utf16.count)
let matches = regex.matches(in: testString, range: stringRange)
var result: [[String]] = []
for match in matches {
var groups: [String] = []
for rangeIndex in 1 ..< match.numberOfRanges {
let nsRange = match.range(at: rangeIndex)
guard !NSEqualRanges(nsRange, NSMakeRange(NSNotFound, 0)) else { continue }
let string = (testString as NSString).substring(with: nsRange)
groups.append(string)
}
if !groups.isEmpty {
result.append(groups)
}
}
print(result)
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 Swift 5.2, please visit: https://developer.apple.com/documentation/foundation/nsregularexpression