import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class Example {
public static void main(String[] args) {
final String regex = "(ON|JOIN|WHERE|OR|AND)(\\s*?.*)(?<func_name>[a-zA-Z]*?\\((?<params>[\\w\\s\\.]+)\\))";
final String string = "\n"
+ "-- From: https://bertwagner.com/posts/how-to-search-and-destroy-non-sargable-queries-on-your-server/\n\n"
+ "SELECT Name\n"
+ "FROM dbo.CoffeeInventory\n"
+ "WHERE \n"
+ " CONVERT(CHAR(10),CreateDate,121) = '2017-08-19'\n\n\n"
+ "SELECT Name, CreateDate\n"
+ "FROM dbo.CoffeeInventory\n"
+ "WHERE DAY(CreateDate) = 19\n"
+ "We want to make it SARGable by doing this instead:\n\n"
+ "SELECT Name, CreateDate\n"
+ "FROM dbo.CoffeeInventory\n"
+ "WHERE \n"
+ " CreateDate >= '2017-08-19 00:00:00' \n"
+ " AND CreateDate < '2017-08-20 00:00:00'\n\n\n"
+ "---- REAL WORLD\n\n"
+ "SELECT \n"
+ " a.rowid \n"
+ " , LTRIM(RTRIM(a.ordnum)) AS ordnum \n"
+ " , LTRIM(RTRIM(a.itemnum)) AS itemnum \n"
+ " , LTRIM(RTRIM(a.descript)) AS descript \n"
+ " , a.ordtype \n"
+ " , LTRIM(RTRIM(a.name)) AS name \n"
+ " , LTRIM(RTRIM(a.number)) AS number \n"
+ " , a.qtyord \n"
+ " , a.extension \n"
+ " , a.price \n"
+ " , a.deldate \n"
+ " , a.sfstatus \n"
+ " , LTRIM(RTRIM(a.slpcode)) AS slpcode \n"
+ " , a.grouporder \n"
+ " , a.allocated \n"
+ " , a.allocamt \n"
+ " , ISNULL(LTRIM(RTRIM(a.slpname)), '') AS slpname \n"
+ " , CASE WHEN b.custponum LIKE '%REPL%' OR b.custponum LIKE 'RMA%' THEN 1 ELSE 0 END AS IsReturn \n"
+ " , LTRIM(RTRIM(b.custponum)) AS custponum \n"
+ " , a.alloc_flag \n"
+ " , a.keyid \n"
+ " , d.comment \n"
+ " , f.comment AS custcomment \n"
+ " , b.orddate \n"
+ " , a.etadate \n"
+ " , a.EtaDisplayString \n"
+ " , LTRIM(RTRIM(a.filenum)) AS filenum \n"
+ " , a.credithold_cust \n"
+ " , a.credithold_so \n"
+ " , a.sfstatus_name \n"
+ " , ISNULL(b.ordtot,0) AS ordtot \n"
+ " , b.Comment AS GoldenSoComment \n"
+ " , h.termcode \n"
+ " , h.crlimit \n"
+ " , g.InvoiceTotal -- (h.crlimit - ISNULL(g.InvoiceTotal,0)) AS AvailableCredit \n"
+ " , CASE WHEN \n"
+ " h.crlimit < (h.crlimit - ISNULL(g.InvoiceTotal,0)) \n"
+ " THEN h.crlimit \n"
+ " ELSE (h.crlimit - ISNULL(g.InvoiceTotal,0)) \n"
+ " END AS AvailableCredit \n"
+ " , LTRIM(RTRIM(b.repcode)) AS repcode \n"
+ " , a.shipgroup \n"
+ " , 'readytoship' = CASE WHEN \n"
+ " b.ordtype IN ('S', 'B') \n"
+ " AND ( \n"
+ " -- Credit Card On File \n"
+ " b.termcode IN ('CC') \n"
+ " -- Has Terms and Available Credit \n"
+ " OR CHARINDEX(LEFT(b.termcode,1),'1234567890') > 1 \n"
+ " ) \n"
+ " AND b.credithold = 0 \n"
+ " AND h.credithold = 0 \n"
+ " AND i.whcode IN ('A') \n"
+ " AND b.deldate <= GETDATE() \n"
+ " AND CHARINDEX('RESERVE',UPPER(b.custponum)) = 0 \n"
+ " AND a.alloc_flag = 2 \n"
+ " AND a.shipgroup = 0 \n"
+ " THEN 1 ELSE 0 END \n"
+ " , CASE \n"
+ " WHEN b.paymethod = 'P' THEN 'PREPAID' \n"
+ " WHEN b.paymethod = 'U' THEN 'PICKUP' \n"
+ " WHEN b.paymethod = 'C' THEN 'COLLECT' \n"
+ " WHEN b.paymethod = 'T' THEN 'THIRD PARTY' \n"
+ " ELSE '' \n"
+ " END AS paymethod \n"
+ " , CASE \n"
+ " WHEN b.paymethod = 'P' THEN \n"
+ " CASE \n"
+ " WHEN b.fslid = 1 THEN 'STANDARD' \n"
+ " WHEN b.fslid = 2 THEN 'EXPEDITED' \n"
+ " WHEN b.fslid = 3 THEN 'RUSH' \n"
+ " ELSE '' \n"
+ " END \n"
+ " ELSE '' \n"
+ " END AS fsl \n"
+ " , b.credcard \n"
+ " , b.onetimecard \n"
+ "FROM RSRPT_ItemAllocation a \n"
+ "-- LEFT JOIN ODS.dbo.vwSOORDEHT b ON LTRIM(RTRIM(a.ordnum)) = LTRIM(RTRIM(b.ordnum)) \n"
+ "LEFT JOIN data_SODetailProperties d ON a.keyid = d.keyid \n"
+ "LEFT JOIN data_CustomerProperties f ON a.number = f.number \n"
+ "LEFT JOIN ( \n"
+ " SELECT \n"
+ " [cust no] AS number \n"
+ " , SUM([Amount Open]) AS InvoiceTotal \n"
+ " FROM pivot_ARAging \n"
+ " GROUP BY [cust no] \n"
+ ") g ON a.number = g.number \n"
+ "INNER JOIN ODS..vwarcustmm h ON a.number = h.number \n"
+ "LEFT JOIN ODS..soordedt i \n"
+ " ON a.ordnum = LTRIM(RTRIM(i.ordnum)) AND a.keyid = LTRIM(RTRIM(i.keyid)) \n\n"
+ "-- short query\n\n"
+ "-- Newlined\n"
+ " select RTRIM(LTRIM(ordnum)) \n"
+ " as ordnum_trimmed from ODS.dbo.vwSOORDEHT \n"
+ " where ordnum = LTRIM(RTRIM(ordnum))\n\n"
+ "-- Inlined\n"
+ " select RTRIM(LTRIM(ordnum)) as ordnum_trimmed from ODS.dbo.vwSOORDEHT where ordnum = LTRIM(RTRIM(@ordnum))";
final Pattern pattern = Pattern.compile(regex, Pattern.MULTILINE);
final Matcher matcher = pattern.matcher(string);
while (matcher.find()) {
System.out.println("Full match: " + matcher.group(0));
for (int i = 1; i <= matcher.groupCount(); i++) {
System.out.println("Group " + i + ": " + matcher.group(i));
}
}
}
}
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 Java, please visit: https://docs.oracle.com/javase/7/docs/api/java/util/regex/Pattern.html