Regular Expressions 101

Save & Share

Flavor

  • PCRE2 (PHP >=7.3)
  • PCRE (PHP <7.3)
  • ECMAScript (JavaScript)
  • Python
  • Golang
  • Java 8
  • .NET 7.0 (C#)
  • Rust
  • Regex Flavor Guide

Function

  • Match
  • Substitution
  • List
  • Unit Tests

Tools

Sponsors
There are currently no sponsors. Become a sponsor today!
An explanation of your regex will be automatically generated as you type.
Detailed match information will be displayed here automatically.
  • All Tokens
  • Common Tokens
  • General Tokens
  • Anchors
  • Meta Sequences
  • Quantifiers
  • Group Constructs
  • Character Classes
  • Flags/Modifiers
  • Substitution
  • A single character of: a, b or c
    [abc]
  • A character except: a, b or c
    [^abc]
  • A character in the range: a-z
    [a-z]
  • A character not in the range: a-z
    [^a-z]
  • A character in the range: a-z or A-Z
    [a-zA-Z]
  • Any single character
    .
  • Alternate - match either a or b
    a|b
  • Any whitespace character
    \s
  • Any non-whitespace character
    \S
  • Any digit
    \d
  • Any non-digit
    \D
  • Any word character
    \w
  • Any non-word character
    \W
  • Non-capturing group
    (?:...)
  • Capturing group
    (...)
  • Zero or one of a
    a?
  • Zero or more of a
    a*
  • One or more of a
    a+
  • Exactly 3 of a
    a{3}
  • 3 or more of a
    a{3,}
  • Between 3 and 6 of a
    a{3,6}
  • Start of string
    ^
  • End of string
    $
  • A word boundary
    \b
  • Non-word boundary
    \B

Regular Expression
No Match

/
/
gm

Test String

Code Generator

Language

Generated Code

import java.util.regex.Matcher; import java.util.regex.Pattern; public class Example { public static void main(String[] args) { final String regex = "(LEFT|RIGHT|CROSS)\\s{0,}(INNER|OUTER){0,}\\s{0,}JOIN.{0,}$|FROM.{0,}$"; final String string = "--------------------------------------------------------\n" + "-- INSERT PLACEHOLDER RECORDS (fill in query later)\n" + "--------------------------------------------------------\n\n" + "INSERT INTO ITGIS.GLOBALSEARCHMASTER (DISPLAYTYPE, XSCHEMA, XTABLE, XUIDCOLUMN, XSHAPECOLUMN, XSHAPETYPE, QUERYTEXT, ENABLED) VALUES ('OPERATING STATION', 'ITGIS', 'COMP_OP_STN', 'OBJECTID', 'SHAPE', 'POINT', 'OK ', 'Y');\n" + "INSERT INTO ITGIS.GLOBALSEARCHMASTER (DISPLAYTYPE, XSCHEMA, XTABLE, XUIDCOLUMN, XSHAPECOLUMN, XSHAPETYPE, QUERYTEXT, ENABLED) VALUES ('CROSSING', 'ENGXING', 'CI_LOCATIONANDCLASSIFICATION', 'ASSET_PK', 'SHAPE', 'POINT', 'OK ', 'Y');\n" + "INSERT INTO ITGIS.GLOBALSEARCHMASTER (DISPLAYTYPE, XSCHEMA, XTABLE, XUIDCOLUMN, XSHAPECOLUMN, XSHAPETYPE, QUERYTEXT, ENABLED) VALUES ('MILEPOST', 'EGIS', 'ENG_MPMARK', 'OBJECTID', 'SHAPE', 'POINT', 'OK ', 'Y');\n" + "INSERT INTO ITGIS.GLOBALSEARCHMASTER (DISPLAYTYPE, XSCHEMA, XTABLE, XUIDCOLUMN, XSHAPECOLUMN, XSHAPETYPE, QUERYTEXT, ENABLED) VALUES ('SWITCH', 'EGIS', 'ENG_SWITCH', 'OBJECTID', 'SHAPE', 'POINT', 'OK ', 'Y');\n" + "INSERT INTO ITGIS.GLOBALSEARCHMASTER (DISPLAYTYPE, XSCHEMA, XTABLE, XUIDCOLUMN, XSHAPECOLUMN, XSHAPETYPE, QUERYTEXT, ENABLED) VALUES ('TOWER', 'EGIS', 'ENG_TOWER', 'OBJECTID', 'SHAPE', 'POINT', 'OK ', 'Y');\n" + "INSERT INTO ITGIS.GLOBALSEARCHMASTER (DISPLAYTYPE, XSCHEMA, XTABLE, XUIDCOLUMN, XSHAPECOLUMN, XSHAPETYPE, QUERYTEXT, ENABLED) VALUES ('AEI SCANNER', 'ITGIS', 'COMP_AEI_SCNR', 'OBJECTID', 'SHAPE', 'POINT', 'OK ', 'Y');\n" + "INSERT INTO ITGIS.GLOBALSEARCHMASTER (DISPLAYTYPE, XSCHEMA, XTABLE, XUIDCOLUMN, XSHAPECOLUMN, XSHAPETYPE, QUERYTEXT, ENABLED) VALUES ('NSPD LOCATION', 'ITGIS', 'NSPD_LOCATIONS', 'OBJECTID', 'SHAPE', 'POINT', 'OK ', 'Y');\n" + "INSERT INTO ITGIS.GLOBALSEARCHMASTER (DISPLAYTYPE, XSCHEMA, XTABLE, XUIDCOLUMN, XSHAPECOLUMN, XSHAPETYPE, QUERYTEXT, ENABLED) VALUES ('NSPD POI', 'NSPD', 'NSPD_POI', 'ID', 'SHAPE', 'POINT', 'OK ', 'Y');\n" + "INSERT INTO ITGIS.GLOBALSEARCHMASTER (DISPLAYTYPE, XSCHEMA, XTABLE, XUIDCOLUMN, XSHAPECOLUMN, XSHAPETYPE, QUERYTEXT, ENABLED) VALUES ('MILEPOST 100S', 'ITGIS', 'ITGIS_MP100S', 'OBJECTID', 'SHAPE', 'POINT', 'OK ', 'Y');\n\n" + "--------------------------------------------------------\n" + "-- UPDATE ACTUAL QUERIES\n" + "--------------------------------------------------------\n\n" + "/* ITGIS.COMP_OP_STN */\n" + "UPDATE ITGIS.GLOBALSEARCHMASTER\n" + "SET QUERYTEXT =\n" + " q'[\n" + "WITH nsdMatches AS\n" + "(\n" + "SELECT cos.OBJECTID,\n" + "nsd.DIVCODE,\n" + "nsd.DIVDESCR,\n" + "nsd.DIVABBR,\n" + "nsd.DIVNAME\n" + "FROM ITGIS.COMP_OP_STN cos\n" + "CROSS JOIN ITGIS.NS_DIVISIONS nsd\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_RELATE ( cos.SHAPE, nsd.SHAPE, 'mask = anyinteract' ) = 'TRUE'\n" + "AND 1 = 1\n" + "),\n" + "nrzMatches AS\n" + "(\n" + "SELECT cos.OBJECTID,\n" + "nrz.BEAT_ID,\n" + "nrz.AREA_ID,\n" + "nrz.FIELD_OFF,\n" + "nrz.COMMON_NAME NSPD_RESP_ZONE\n" + "FROM ITGIS.COMP_OP_STN cos\n" + "CROSS JOIN ITGIS.NSPD_RESP_ZONE nrz\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_RELATE ( cos.SHAPE, nrz.SHAPE, 'mask = anyinteract' ) = 'TRUE'\n" + "AND 1 = 1\n" + "),\n" + "countyMatches AS\n" + "(\n" + "SELECT cos.OBJECTID,\n" + "usc.STATE,\n" + "usc.COUNTY\n" + "FROM ITGIS.COMP_OP_STN cos\n" + "CROSS JOIN PUBDATA.US_COUNTIES usc\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_RELATE ( usc.SHAPE, cos.SHAPE, 'mask = anyinteract' ) = 'TRUE'\n" + "AND 1 = 1\n" + "),\n" + "cityBoundaryMatches AS\n" + "(\n" + "SELECT cos.OBJECTID,\n" + "uscb.NAME CITY\n" + "FROM ITGIS.COMP_OP_STN cos\n" + "CROSS JOIN PUBDATA.US_CITIES_BOUNDARIES uscb\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_RELATE ( uscb.SHAPE, cos.SHAPE, 'mask = anyinteract' ) = 'TRUE'\n" + "AND 1 = 1\n" + ")\n" + "SELECT\n" + "***MASTERID***, --MASTERID\n" + "cos.OBJECTID, --XUID\n" + "UPPER(cos.TCNTRL_STN_NAME), --SEARCHTEXT\n" + "UPPER(ITGIS.MAKE_NSPD_MP_DB_PARTS_MANUAL(TRIM(cos.PREFIX), TRIM(cos.SUFFIX), cos.MP, nfsac.DISTRICT, nfsac.BRANCH)), --DISPLAYTEXT\n" + "SDO_CS.MAKE_2D ( cos.SHAPE, 4326 ), --POINT/LINE/POLYGON\n" + "ROUND ( cos.SHAPE.SDO_POINT.y, 6 ), --LATITUDE\n" + "ROUND ( cos.SHAPE.SDO_POINT.x, 6 ), --LONGITUDE\n" + "nrz.BEAT_ID, --BEAT\n" + "UPPER(cos.POSTAL_CD), --ZIPCODE\n" + "UPPER(COALESCE(cityBoundaryMatches.CITY,c.NAME)), --CITY\n" + "UPPER(countyMatches.COUNTY), --COUNTY\n" + "UPPER(COALESCE(CAST(cos.ST_ABBR AS NVARCHAR2(2)),CAST(countyMatches.STATE AS NVARCHAR2(2)))), --STATE\n" + "UPPER(COALESCE(nd.DIVNAME,nsd.DIVNAME)), --DIVISION\n" + "UPPER(nrz.FIELD_OFF), --FIELD_OFFICE\n" + "nrz.AREA_ID, --AREA\n" + "NULL, --JURISDICTION\n" + "NULL, --MUNICIPALITY\n" + "NULL, --LOCATION_ID\n" + "UPPER(ITGIS.MAKE_NSPD_MILEPOST_SHORT(TRIM(cos.PREFIX),TRIM(cos.SUFFIX),cos.MP)), --MILEPOST\n" + "UPPER(TRIM(cos.PREFIX)), --MP_PREFIX\n" + "cos.MP, --MP_NUMBER\n" + "UPPER(TRIM(cos.SUFFIX)), --MP_SUFFIX\n" + "UPPER(cos.TCNTRL_STN_NAME), --NAME\n" + "NULL, --CROSSING_DOT_NUM\n" + "NULL, --CROSSING_FULL_NAME\n" + "NULL --TRACK_SEGMENT_RANGE\n" + "FROM ITGIS.COMP_OP_STN cos\n" + "LEFT JOIN nsdMatches nsd\n" + "ON cos.OBJECTID = nsd.OBJECTID\n" + "LEFT JOIN nrzMatches nrz\n" + "ON cos.OBJECTID = nrz.OBJECTID\n" + "LEFT JOIN cityBoundaryMatches\n" + "ON cos.OBJECTID = cityBoundaryMatches.OBJECTID\n" + "LEFT JOIN countyMatches\n" + "ON cos.OBJECTID = countyMatches.OBJECTID\n" + "LEFT JOIN ITGIS.NSPD_LOCATIONS nl\n" + "ON nrz.BEAT_ID = nl.BEAT\n" + "LEFT JOIN ITGIS.NS_DIVISIONS nd\n" + "ON cos.DIVCODE = nd.DIVCODE\n" + "LEFT JOIN NFSAPP.CONTROL nfsac\n" + "ON(ITGIS.MAKE_LINEID(cos.DIVCODE, cos.PREFIX, cos.SUFFIX) = nfsac.LINE_ID AND cos.MP BETWEEN nfsac.LMP AND nfsac.HMP)\n" + "CROSS JOIN PUBDATA.US_CITIES c\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_NN ( c.SHAPE, cos.SHAPE, 'sdo_num_res = 1', 1 ) = 'TRUE'\n" + "--AND ROWNUM = 1\n" + "AND 1 = 1\n" + "]'\n" + "WHERE 1 = 1\n" + " AND XSCHEMA = 'ITGIS'\n" + " AND XTABLE = 'COMP_OP_STN'\n" + " AND 1 = 1;\n\n" + "/* ENGXING.DOTLIST */\n" + "UPDATE ITGIS.GLOBALSEARCHMASTER\n" + "SET QUERYTEXT = TO_CLOB(q'[\n" + "WITH nsdMatches AS\n" + "(\n" + "SELECT cils.CROSSINGID,\n" + "nsd.DIVCODE,\n" + "nsd.DIVDESCR,\n" + "nsd.DIVABBR,\n" + "nsd.DIVNAME\n" + "FROM ENGXING.CI_LOCATIONANDCLASSIFICATION cils\n" + "CROSS JOIN ITGIS.NS_DIVISIONS nsd\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_RELATE(cils.SHAPE, nsd.SHAPE, 'mask = anyinteract') = 'TRUE'\n" + "AND 1 = 1\n" + "),\n" + "nrzMatches AS\n" + "(\n" + "SELECT cils.CROSSINGID,\n" + "nrz.BEAT_ID,\n" + "nrz.AREA_ID,\n" + "nrz.FIELD_OFF,\n" + "nrz.COMMON_NAME NSPD_RESP_ZONE\n" + "FROM ENGXING.CI_LOCATIONANDCLASSIFICATION cils\n" + "CROSS JOIN ITGIS.NSPD_RESP_ZONE nrz\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_RELATE(cils.SHAPE, nrz.SHAPE, 'mask = anyinteract') = 'TRUE'\n" + "AND 1 = 1\n" + "),\n" + "countyMatches AS\n" + "(\n" + "SELECT cils.CROSSINGID,\n" + "usc.STATE,\n" + "usc.COUNTY\n" + "FROM ENGXING.CI_LOCATIONANDCLASSIFICATION cils\n" + "CROSS JOIN PUBDATA.US_COUNTIES usc\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_RELATE(usc.SHAPE, cils.SHAPE, 'mask = anyinteract') = 'TRUE'\n" + "AND 1 = 1\n" + "),\n" + "cityBoundaryMatches AS\n" + "(\n" + "SELECT cils.CROSSINGID,\n" + "uscb.NAME CITY\n" + "FROM ENGXING.CI_LOCATIONANDCLASSIFICATION cils\n" + "CROSS JOIN PUBDATA.US_CITIES_BOUNDARIES uscb\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_RELATE(uscb.SHAPE, cils.SHAPE, 'mask = anyinteract') = 'TRUE'\n" + "AND 1 = 1\n" + ")\n" + "SELECT\n" + "***MASTERID***, --MASTERID\n" + "cils.ASSET_PK,\n" + "CASE WHEN q.INVDATE IS NOT NULL THEN UPPER(ITGIS.MAKE_NSPD_MILEPOST_SHORT(cior.PRFXMILEPOST,TRIM(cior.SFXMILEPOST),TRIM(cior.MILEPOST)) || ' ' || cior.RRSUBDIV || ' ' || cils.STREET || ' (' || cils.HIGHWAY || ')') ELSE UPPER( ITGIS.MAKE_NSPD_MILEPOST_SHORT(cior.PRFXMILEPOST,TRIM(cior.SFXMILEPOST),TRIM(cior.MILEPOST)) || ' ' || ec.STREETNAME) END, --SEARCHTEXT\n" + "CASE WHEN q.INVDATE IS NOT NULL THEN UPPER(ITGIS.MAKE_NSPD_MILEPOST_SHORT(cior.PRFXMILEPOST,TRIM(cior.SFXMILEPOST),TRIM(cior.MILEPOST)) || ' ' || cior.RRSUBDIV) ELSE UPPER(ITGIS.MAKE_NSPD_MP_DB_PARTS_MANUAL(TRIM(ec.PREFIX), TRIM(ec.SUFFIX), ec.MP, nfsac.DISTRICT, nfsac.BRANCH)) END, --DISPLAYTEXT\n" + "CASE WHEN q.INVDATE IS NOT NULL THEN SDO_CS.MAKE_2D ( cils.SHAPE, 4326) ELSE SDO_CS.MAKE_2D ( ec.SHAPE, 4326) END, --POINT/LINE/POLYGON\n" + "CASE WHEN q.INVDATE IS NOT NULL THEN ROUND(cils.SHAPE.SDO_POINT.y, 6) ELSE ROUND(ec.SHAPE.SDO_POINT.y, 6) END, --LATITUDE\n" + "CASE WHEN q.INVDATE IS NOT NULL THEN ROUND(cils.SHAPE.SDO_POINT.x, 6) ELSE ROUND(ec.SHAPE.SDO_POINT.x, 6) END, --LONGITUDE\n" + "nrz.BEAT_ID, --BEAT\n" + "NULL, --ZIPCODE\n" + "CASE WHEN q.INVDATE IS NOT NULL THEN UPPER(CAST(cils.CITYNAME AS NVARCHAR2(100))) ELSE UPPER(COALESCE(cityBoundaryMatches.CITY, c.NAME)) END, --CITY\n" + "CASE WHEN q.INVDATE IS NOT NULL THEN UPPER(CAST(cils.COUNTYNAME AS NVARCHAR2(50))) ELSE UPPER(countyMatches.COUNTY) END, --COUNTY\n" + "CASE WHEN q.INVDATE IS NOT NULL THEN UPPER(CAST(cils.STATENAME AS NVARCHAR2(2))) ELSE UPPER(CAST(countyMatches.STATE AS NVARCHAR2(2))) END, --STATE\n" + "CASE WHEN q.INVDATE IS NOT NULL THEN UPPER(CAST(cior.RRDIV AS NVARCHAR2(50))) ELSE UPPER(COALESCE(nd.DIVNAME, nsd.DIVNAME)) END, --DIVISION\n" + "UPPER(nrz.FIELD_OFF), --FIELD_OFFICE\n" + "nrz.AREA_ID, --AREA]') || TO_CLOB(q'[\n" + "NULL, --JURISDICTION\n" + "NULL, --MUNICIPALITY\n" + "CASE WHEN q.INVDATE IS NOT NULL THEN CASE cils.POSXING WHEN '1' THEN 'AG' WHEN '2' THEN 'UG' WHEN '3' THEN 'OG' ELSE '' END ELSE CASE CAST(ec.GRADETYPE AS VARCHAR2 ( 50)) WHEN 'ATGRADE' THEN 'AG' WHEN 'RRUNDER' THEN 'UG' WHEN 'RROVER' THEN 'OG' ELSE '' END END, --LOCATION_ID\n" + "CASE WHEN q.INVDATE IS NOT NULL THEN ITGIS.MAKE_NSPD_MILEPOST_SHORT ( TRIM(cior.PRFXMILEPOST), TRIM(cior.SFXMILEPOST), cior.MILEPOST) ELSE ITGIS.MAKE_NSPD_MILEPOST_SHORT ( TRIM(ec.PREFIX), TRIM(ec.SUFFIX), CAST ( CASE WHEN REGEXP_LIKE(TRIM(ec.MPLOCATION),'^\\d+(\\.\\d+)?$','') THEN ec.MPLOCATION ELSE NULL END AS NUMBER )) END, --MILEPOST\n" + "CASE WHEN q.INVDATE IS NOT NULL THEN TRIM(cior.PRFXMILEPOST) ELSE TRIM(ec.PREFIX) END, --MP_PREFIX\n" + "CASE WHEN q.INVDATE IS NOT NULL THEN cior.MILEPOST ELSE CAST ( CASE WHEN REGEXP_LIKE(TRIM(ec.MPLOCATION),'^\\d+(\\.\\d+)?$','') THEN ec.MPLOCATION ELSE NULL END AS NUMBER ) END, --MP_NUMBER\n" + "CASE WHEN q.INVDATE IS NOT NULL THEN TRIM(cior.SFXMILEPOST) ELSE TRIM(ec.SUFFIX) END, --MP_SUFFIX\n" + "CASE WHEN q.INVDATE IS NOT NULL THEN UPPER(cils.STREET || ' (' || cils.HIGHWAY || ')') ELSE UPPER(ec.STREETNAME) END, --NAME\n" + "UPPER(dl.DOTNUM), --CROSSING_DOT_NUM\n" + "NULL, --CROSSING_FULL_NAME\n" + "NULL --TRACK_SEGMENT_RANGE\n" + "FROM ENGXING.DOTLIST dl\n" + "INNER JOIN ENGXING.CI_LOCATIONANDCLASSIFICATION cils\n" + "ON dl.DOTNUM = cils.CROSSINGID\n" + "LEFT JOIN ENGXING.INVENTORY_QUE q\n" + "ON dl.DOTNUM = q.DOTNUM\n" + "LEFT JOIN nsdMatches nsd\n" + "ON dl.DOTNUM = nsd.CROSSINGID\n" + "LEFT JOIN ENGXING.CI_OPERATINGRAILROAD cior\n" + "ON dl.DOTNUM = cior.CROSSINGID\n" + "LEFT JOIN nrzMatches nrz\n" + "ON cils.CROSSINGID = nrz.CROSSINGID\n" + "LEFT JOIN cityBoundaryMatches\n" + "ON cils.CROSSINGID = cityBoundaryMatches.CROSSINGID\n" + "LEFT JOIN countyMatches\n" + "ON cils.CROSSINGID = countyMatches.CROSSINGID\n" + "LEFT JOIN ITGIS.NSPD_LOCATIONS nl\n" + "ON nrz.BEAT_ID = nl.BEAT\n" + "LEFT JOIN ITGIS.NS_DIVISIONS nd\n" + "ON cior.DIVCODE = nd.DIVCODE\n" + "LEFT JOIN ENG.ENG_CROSSING ec\n" + "ON(dl.DOTNUM = ec.DOTNUM AND ec.SUBLOC = 'C')\n" + "LEFT JOIN NFSAPP.CONTROL nfsac\n" + "ON(ITGIS.MAKE_LINEID(ec.DIVCODE, ec.PREFIX, ec.SUFFIX) = nfsac.LINE_ID AND ec.MP BETWEEN nfsac.LMP AND nfsac.HMP)\n" + "CROSS JOIN PUBDATA.US_CITIES c\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_NN(c.SHAPE, cils.SHAPE, 'sdo_num_res = 1', 1) = 'TRUE'\n" + "--AND ROWNUM = 1\n" + "AND 1 = 1\n" + "]')\n" + "WHERE 1 = 1\n" + " AND XSCHEMA = 'ENGXING'\n" + " AND XTABLE = 'CI_LOCATIONANDCLASSIFICATION'\n" + " AND 1 = 1;\n\n" + "/* EGIS.ENG_MPMARK */\n" + "UPDATE ITGIS.GLOBALSEARCHMASTER\n" + "SET QUERYTEXT =\n" + " q'[\n" + "WITH nsdMatches AS\n" + "(\n" + "SELECT mp.OBJECTID,\n" + "nsd.DIVCODE,\n" + "nsd.DIVDESCR,\n" + "nsd.DIVABBR,\n" + "nsd.DIVNAME\n" + "FROM EGIS.ENG_MPMARK mp\n" + "CROSS JOIN ITGIS.NS_DIVISIONS nsd\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_RELATE ( mp.SHAPE, nsd.SHAPE, 'mask = anyinteract' ) = 'TRUE'\n" + "AND 1 = 1\n" + "),\n" + "nrzMatches AS\n" + "(\n" + "SELECT mp.OBJECTID,\n" + "nrz.BEAT_ID,\n" + "nrz.AREA_ID,\n" + "nrz.FIELD_OFF,\n" + "nrz.COMMON_NAME NSPD_RESP_ZONE\n" + "FROM EGIS.ENG_MPMARK mp\n" + "CROSS JOIN ITGIS.NSPD_RESP_ZONE nrz\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_RELATE ( mp.SHAPE, nrz.SHAPE, 'mask = anyinteract' ) = 'TRUE'\n" + "AND 1 = 1\n" + "),\n" + "countyMatches AS\n" + "(\n" + "SELECT mp.OBJECTID,\n" + "usc.STATE,\n" + "usc.COUNTY\n" + "FROM EGIS.ENG_MPMARK mp\n" + "CROSS JOIN PUBDATA.US_COUNTIES usc\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_RELATE ( usc.SHAPE, mp.SHAPE, 'mask = anyinteract' ) = 'TRUE'\n" + "AND 1 = 1\n" + "),\n" + "cityBoundaryMatches AS\n" + "(\n" + "SELECT mp.OBJECTID,\n" + "uscb.NAME CITY\n" + "FROM EGIS.ENG_MPMARK mp\n" + "CROSS JOIN PUBDATA.US_CITIES_BOUNDARIES uscb\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_RELATE ( uscb.SHAPE, mp.SHAPE, 'mask = anyinteract' ) = 'TRUE'\n" + "AND 1 = 1\n" + ")\n" + "SELECT\n" + "***MASTERID***, --MASTERID\n" + "mp.OBJECTID, --XUID\n" + "UPPER(ITGIS.MAKE_NSPD_MP_DB_PARTS_MANUAL(TRIM(mp.PREFIX), TRIM(mp.SUFFIX), mp.MPMARKER, nfsac.DISTRICT, nfsac.BRANCH)), --SEARCHTEXT\n" + "UPPER(ITGIS.MAKE_NSPD_MP_DB_PARTS_MANUAL(TRIM(mp.PREFIX), TRIM(mp.SUFFIX), mp.MPMARKER, nfsac.DISTRICT, nfsac.BRANCH)), --DISPLAYTEXT\n" + "SDO_CS.MAKE_2D ( mp.SHAPE, 4326 ), --POINT/LINE/POLYGON\n" + "ROUND ( mp.SHAPE.SDO_POINT.y, 6 ), --LATITUDE\n" + "ROUND ( mp.SHAPE.SDO_POINT.x, 6 ), --LONGITUDE\n" + "nrz.BEAT_ID, --BEAT\n" + "NULL, --ZIPCODE\n" + "UPPER(COALESCE(cityBoundaryMatches.CITY,c.NAME)), --CITY\n" + "UPPER(countyMatches.COUNTY), --COUNTY\n" + "UPPER(countyMatches.STATE), --STATE\n" + "UPPER(COALESCE(nd.DIVNAME,nsd.DIVNAME)), --DIVISION\n" + "UPPER(nrz.FIELD_OFF), --FIELD_OFFICE\n" + "nrz.AREA_ID, --AREA\n" + "UPPER(nl.OFFICEDESCRIPTION), --JURISDICTION\n" + "UPPER(COALESCE(cityBoundaryMatches.CITY,c.NAME)), --MUNICIPALITY\n" + "NULL, --LOCATION_ID\n" + "UPPER(ITGIS.MAKE_NSPD_MILEPOST_SHORT ( TRIM(mp.PREFIX), TRIM(mp.SUFFIX), mp.MPMARKER )), --MILEPOST\n" + "UPPER(TRIM(mp.PREFIX)), --MP_PREFIX\n" + "mp.MPMARKER, --MP_NUMBER\n" + "UPPER(TRIM(mp.SUFFIX)), --MP_SUFFIX\n" + "UPPER(ITGIS.MAKE_NSPD_MP_DB_PARTS_MANUAL(TRIM(mp.PREFIX), TRIM(mp.SUFFIX), mp.MPMARKER, nfsac.DISTRICT, nfsac.BRANCH)), --NAME\n" + "NULL, --CROSSING_DOT_NUM\n" + "NULL, --CROSSING_FULL_NAME\n" + "NULL --TRACK_SEGMENT_RANGE\n" + "FROM EGIS.ENG_MPMARK mp\n" + "LEFT JOIN nrzMatches nrz\n" + "ON mp.OBJECTID = nrz.OBJECTID\n" + "LEFT JOIN nsdMatches nsd\n" + "ON mp.OBJECTID = nsd.OBJECTID\n" + "LEFT JOIN cityBoundaryMatches\n" + "ON mp.OBJECTID = cityBoundaryMatches.OBJECTID\n" + "LEFT JOIN countyMatches\n" + "ON mp.OBJECTID = countyMatches.OBJECTID\n" + "LEFT JOIN ITGIS.NSPD_LOCATIONS nl\n" + "ON nrz.BEAT_ID = nl.BEAT\n" + "LEFT JOIN ITGIS.NS_DIVISIONS nd\n" + "ON mp.DIVCODE = nd.DIVCODE\n" + "LEFT JOIN NFSAPP.CONTROL nfsac\n" + "ON(ITGIS.MAKE_LINEID(mp.DIVCODE, mp.PREFIX, mp.SUFFIX) = nfsac.LINE_ID AND mp.MPMARKER BETWEEN nfsac.LMP AND nfsac.HMP)\n" + "CROSS JOIN PUBDATA.US_CITIES c\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_NN ( c.SHAPE, mp.SHAPE, 'sdo_num_res = 1', 1 ) = 'TRUE'\n" + "--AND ROWNUM = 1\n" + "AND 1 = 1\n" + "]'\n" + "WHERE 1 = 1\n" + " AND XSCHEMA = 'EGIS'\n" + " AND XTABLE = 'ENG_MPMARK'\n" + " AND 1 = 1;\n\n" + "/* EGIS.ENG_SWITCH */\n" + "UPDATE ITGIS.GLOBALSEARCHMASTER\n" + "SET QUERYTEXT =\n" + " q'[\n" + "WITH nsdMatches AS\n" + "(\n" + "SELECT s.OBJECTID,\n" + "nsd.DIVCODE,\n" + "nsd.DIVDESCR,\n" + "nsd.DIVABBR,\n" + "nsd.DIVNAME\n" + "FROM EGIS.ENG_SWITCH s\n" + "CROSS JOIN ITGIS.NS_DIVISIONS nsd\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_RELATE ( s.SHAPE, nsd.SHAPE, 'mask = anyinteract' ) = 'TRUE'\n" + "AND 1 = 1\n" + "),\n" + "nrzMatches AS\n" + "(\n" + "SELECT s.OBJECTID,\n" + "nrz.BEAT_ID,\n" + "nrz.AREA_ID,\n" + "nrz.FIELD_OFF,\n" + "nrz.COMMON_NAME NSPD_RESP_ZONE\n" + "FROM EGIS.ENG_SWITCH s\n" + "CROSS JOIN ITGIS.NSPD_RESP_ZONE nrz\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_RELATE ( s.SHAPE, nrz.SHAPE, 'mask = anyinteract' ) = 'TRUE'\n" + "AND 1 = 1\n" + "),\n" + "countyMatches AS\n" + "(\n" + "SELECT s.OBJECTID,\n" + "usc.STATE,\n" + "usc.COUNTY\n" + "FROM EGIS.ENG_SWITCH s\n" + "CROSS JOIN PUBDATA.US_COUNTIES usc\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_RELATE ( usc.SHAPE, s.SHAPE, 'mask = anyinteract' ) = 'TRUE'\n" + "AND 1 = 1\n" + "),\n" + "cityBoundaryMatches AS\n" + "(\n" + "SELECT s.OBJECTID,\n" + "uscb.NAME CITY\n" + "FROM EGIS.ENG_SWITCH s\n" + "CROSS JOIN PUBDATA.US_CITIES_BOUNDARIES uscb\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_RELATE ( uscb.SHAPE, s.SHAPE, 'mask = anyinteract' ) = 'TRUE'\n" + "AND 1 = 1\n" + ")\n" + "SELECT\n" + "***MASTERID***, --MASTERID\n" + "s.OBJECTID, --XUID\n" + "UPPER(ITGIS.MAKE_NSPD_MP_DB_PARTS_MANUAL(TRIM(s.PREFIX), TRIM(s.SUFFIX), s.MP, nfsac.DISTRICT, nfsac.BRANCH)), --SEARCHTEXT\n" + "UPPER(ITGIS.MAKE_NSPD_MP_DB_PARTS_MANUAL(TRIM(s.PREFIX), TRIM(s.SUFFIX), s.MP, nfsac.DISTRICT, nfsac.BRANCH)), --DISPLAYTEXT\n" + "SDO_CS.MAKE_2D ( s.SHAPE, 4326 ), --POINT/LINE/POLYGON\n" + "ROUND ( s.SHAPE.SDO_POINT.y, 6 ), --LATITUDE\n" + "ROUND ( s.SHAPE.SDO_POINT.x, 6 ), --LONGITUDE\n" + "nrz.BEAT_ID, --BEAT\n" + "NULL, --ZIPCODE\n" + "UPPER(COALESCE(cityBoundaryMatches.CITY,c.NAME)), --CITY\n" + "UPPER(countyMatches.COUNTY), --COUNTY\n" + "UPPER(countyMatches.STATE), --STATE\n" + "UPPER(COALESCE(nd.DIVNAME,nsd.DIVNAME)), --DIVISION\n" + "UPPER(nrz.FIELD_OFF), --FIELD_OFFICE\n" + "nrz.AREA_ID, --AREA\n" + "NULL, --JURISDICTION\n" + "NULL, --MUNICIPALITY\n" + "NULL, --LOCATION_ID\n" + "UPPER(ITGIS.MAKE_NSPD_MILEPOST_SHORT(TRIM(s.PREFIX), TRIM(s.SUFFIX), s.MP)), --MILEPOST\n" + "UPPER(TRIM(s.PREFIX)), --MP_PREFIX\n" + "s.MP, --MP_NUMBER\n" + "UPPER(TRIM(s.SUFFIX)), --MP_SUFFIX\n" + "UPPER(ITGIS.MAKE_NSPD_MP_DB_PARTS_MANUAL(TRIM(s.PREFIX), TRIM(s.SUFFIX), s.MP, nfsac.DISTRICT, nfsac.BRANCH)), --NAME\n" + "NULL, --CROSSING_DOT_NUM\n" + "NULL, --CROSSING_FULL_NAME\n" + "NULL --TRACK_SEGMENT_RANGE\n" + "FROM EGIS.ENG_SWITCH s\n" + "LEFT JOIN nsdMatches nsd\n" + "ON s.OBJECTID = nsd.OBJECTID\n" + "LEFT JOIN nrzMatches nrz\n" + "ON s.OBJECTID = nrz.OBJECTID\n" + "LEFT JOIN cityBoundaryMatches\n" + "ON s.OBJECTID = cityBoundaryMatches.OBJECTID\n" + "LEFT JOIN countyMatches\n" + "ON s.OBJECTID = countyMatches.OBJECTID\n" + "LEFT JOIN ITGIS.NSPD_LOCATIONS nl\n" + "ON nrz.BEAT_ID = nl.BEAT\n" + "LEFT JOIN ITGIS.NS_DIVISIONS nd\n" + "ON s.DIVCODE = nd.DIVCODE\n" + "LEFT JOIN NFSAPP.CONTROL nfsac\n" + "ON(ITGIS.MAKE_LINEID(s.DIVCODE, s.PREFIX, s.SUFFIX) = nfsac.LINE_ID AND s.MP BETWEEN nfsac.LMP AND nfsac.HMP)\n" + "CROSS JOIN PUBDATA.US_CITIES c\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_NN ( c.SHAPE, s.SHAPE, 'sdo_num_res = 1', 1 ) = 'TRUE'\n" + "--AND ROWNUM = 1\n" + "AND 1 = 1\n" + "]'\n" + "WHERE 1 = 1\n" + " AND XSCHEMA = 'EGIS'\n" + " AND XTABLE = 'ENG_SWITCH'\n" + " AND 1 = 1;\n\n" + "-- /* EGIS.ENG_TOWER */\n" + "UPDATE ITGIS.GLOBALSEARCHMASTER\n" + "SET QUERYTEXT =\n" + " q'[\n" + "WITH nsdMatches AS\n" + "(\n" + "SELECT t.OBJECTID,\n" + "nsd.DIVCODE,\n" + "nsd.DIVDESCR,\n" + "nsd.DIVABBR,\n" + "nsd.DIVNAME\n" + "FROM EGIS.ENG_TOWER t\n" + "CROSS JOIN ITGIS.NS_DIVISIONS nsd\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_RELATE ( t.SHAPE, nsd.SHAPE, 'mask = anyinteract' ) = 'TRUE'\n" + "AND 1 = 1\n" + "),\n" + "nrzMatches AS\n" + "(\n" + "SELECT t.OBJECTID,\n" + "nrz.BEAT_ID,\n" + "nrz.AREA_ID,\n" + "nrz.FIELD_OFF,\n" + "nrz.COMMON_NAME NSPD_RESP_ZONE\n" + "FROM EGIS.ENG_TOWER t\n" + "CROSS JOIN ITGIS.NSPD_RESP_ZONE nrz\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_RELATE ( t.SHAPE, nrz.SHAPE, 'mask = anyinteract' ) = 'TRUE'\n" + "AND 1 = 1\n" + "),\n" + "countyMatches AS\n" + "(\n" + "SELECT t.OBJECTID,\n" + "usc.STATE,\n" + "usc.COUNTY\n" + "FROM EGIS.ENG_TOWER t\n" + "CROSS JOIN PUBDATA.US_COUNTIES usc\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_RELATE ( usc.SHAPE, t.SHAPE, 'mask = anyinteract' ) = 'TRUE'\n" + "AND 1 = 1\n" + "),\n" + "cityBoundaryMatches AS\n" + "(\n" + "SELECT t.OBJECTID,\n" + "uscb.NAME CITY\n" + "FROM EGIS.ENG_TOWER t\n" + "CROSS JOIN PUBDATA.US_CITIES_BOUNDARIES uscb\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_RELATE ( uscb.SHAPE, t.SHAPE, 'mask = anyinteract' ) = 'TRUE'\n" + "AND 1 = 1\n" + ")\n" + "SELECT\n" + "***MASTERID***, --MASTERID\n" + "t.OBJECTID, --XUID\n" + "UPPER(t.SITENAME || ' ' || ITGIS.MAKE_NSPD_MP_DB_LINEID(t.LINEID, REGEXP_SUBSTR(t.MILEPOSTCONCAT, '\\d{1,}\\.{0,}\\d{0,}'))), --SEARCHTEXT\n" + "UPPER(ITGIS.MAKE_NSPD_MP_DB_LINEID(t.LINEID, REGEXP_SUBSTR(t.MILEPOSTCONCAT, '\\d{1,}\\.{0,}\\d{0,}'))), --DISPLAYTEXT\n" + "SDO_CS.MAKE_2D ( t.SHAPE, 4326 ), --POINT/LINE/POLYGON\n" + "ROUND ( t.SHAPE.SDO_POINT.y, 6 ), --LATITUDE\n" + "ROUND ( t.SHAPE.SDO_POINT.x, 6 ), --LONGITUDE\n" + "nrz.BEAT_ID, --BEAT\n" + "NULL, --ZIPCODE\n" + "UPPER(COALESCE(cityBoundaryMatches.CITY,c.NAME)), --CITY\n" + "UPPER(countyMatches.COUNTY), --COUNTY\n" + "UPPER(countyMatches.STATE), --STATE\n" + "UPPER(COALESCE(nd.DIVNAME,nsd.DIVNAME)), --DIVISION\n" + "UPPER(nrz.FIELD_OFF), --FIELD_OFFICE\n" + "nrz.AREA_ID, --AREA\n" + "NULL, --JURISDICTION\n" + "NULL, --MUNICIPALITY\n" + "NULL, --LOCATION_ID\n" + "UPPER(ITGIS.MAKE_NSPD_MILEPOST_SHORT(ITGIS.MAKE_PREFIX_LINEID(t.LINEID),ITGIS.MAKE_SUFFIX_LINEID(t.LINEID),REGEXP_SUBSTR(t.MILEPOSTCONCAT, '\\d{1,}\\.{0,}\\d{0,}'))), --MILEPOST\n" + "UPPER(ITGIS.MAKE_PREFIX_LINEID(t.LINEID)), --MP_PREFIX\n" + "REGEXP_SUBSTR(t.MILEPOSTCONCAT, '\\d{1,}\\.{0,}\\d{0,}'), --MP_NUMBER\n" + "UPPER(ITGIS.MAKE_SUFFIX_LINEID(t.LINEID)), --MP_SUFFIX\n" + "UPPER(t.SITENAME), --NAME\n" + "NULL, --CROSSING_DOT_NUM\n" + "NULL, --CROSSING_FULL_NAME\n" + "NULL --TRACK_SEGMENT_RANGE\n" + "FROM EGIS.ENG_TOWER t\n" + "LEFT JOIN nsdMatches nsd\n" + "ON t.OBJECTID = nsd.OBJECTID\n" + "LEFT JOIN nrzMatches nrz\n" + "ON t.OBJECTID = nrz.OBJECTID\n" + "LEFT JOIN cityBoundaryMatches\n" + "ON t.OBJECTID = cityBoundaryMatches.OBJECTID\n" + "LEFT JOIN countyMatches\n" + "ON t.OBJECTID = countyMatches.OBJECTID\n" + "LEFT JOIN ITGIS.NSPD_LOCATIONS nl\n" + "ON nrz.BEAT_ID = nl.BEAT\n" + "LEFT JOIN ITGIS.NS_DIVISIONS nd\n" + "ON SUBSTR ( t.LINEID, 0, 2 ) = nd.DIVCODE\n" + "CROSS JOIN PUBDATA.US_CITIES c\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_NN ( c.SHAPE, t.SHAPE, 'sdo_num_res = 1', 1 ) = 'TRUE'\n" + "--AND ROWNUM = 1\n" + "AND 1 = 1\n" + "]'\n" + "WHERE 1 = 1\n" + " AND XSCHEMA = 'EGIS'\n" + " AND XTABLE = 'ENG_TOWER'\n" + " AND 1 = 1;\n\n" + "/* ITGIS.COMP_AEI_SCNR */\n" + "UPDATE ITGIS.GLOBALSEARCHMASTER\n" + "SET QUERYTEXT =\n" + " q'[\n" + "WITH nsdMatches AS\n" + "(\n" + "SELECT s.OBJECTID,\n" + "nsd.DIVCODE,\n" + "nsd.DIVDESCR,\n" + "nsd.DIVABBR,\n" + "nsd.DIVNAME\n" + "FROM ITGIS.COMP_AEI_SCNR s\n" + "CROSS JOIN ITGIS.NS_DIVISIONS nsd\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_RELATE ( s.SHAPE, nsd.SHAPE, 'mask = anyinteract' ) = 'TRUE'\n" + "AND 1 = 1\n" + "),\n" + "nrzMatches AS\n" + "(\n" + "SELECT s.OBJECTID,\n" + "nrz.BEAT_ID,\n" + "nrz.AREA_ID,\n" + "nrz.FIELD_OFF,\n" + "nrz.COMMON_NAME NSPD_RESP_ZONE\n" + "FROM ITGIS.COMP_AEI_SCNR s\n" + "CROSS JOIN ITGIS.NSPD_RESP_ZONE nrz\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_RELATE ( s.SHAPE, nrz.SHAPE, 'mask = anyinteract' ) = 'TRUE'\n" + "AND 1 = 1\n" + "),\n" + "countyMatches AS\n" + "(\n" + "SELECT s.OBJECTID,\n" + "usc.STATE,\n" + "usc.COUNTY\n" + "FROM ITGIS.COMP_AEI_SCNR s\n" + "CROSS JOIN PUBDATA.US_COUNTIES usc\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_RELATE ( usc.SHAPE, s.SHAPE, 'mask = anyinteract' ) = 'TRUE'\n" + "AND 1 = 1\n" + "),\n" + "cityBoundaryMatches AS\n" + "(\n" + "SELECT s.OBJECTID,\n" + "uscb.NAME CITY\n" + "FROM ITGIS.COMP_AEI_SCNR s\n" + "CROSS JOIN PUBDATA.US_CITIES_BOUNDARIES uscb\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_RELATE ( uscb.SHAPE, s.SHAPE, 'mask = anyinteract' ) = 'TRUE'\n" + "AND 1 = 1\n" + ")\n" + "SELECT\n" + "***MASTERID***, --MASTERID\n" + "s.OBJECTID, --XUID\n" + "UPPER(s.NAME || ' ' || ITGIS.MAKE_NSPD_MILEPOST_SHORT(TRIM(s.PREFIX),TRIM(s.SUFFIX),s.MP)), --SEARCHTEXT\n" + "UPPER(ITGIS.MAKE_NSPD_MP_DB_PARTS_MANUAL(TRIM(s.PREFIX), TRIM(s.SUFFIX), s.MP, nfsac.DISTRICT, nfsac.BRANCH)), --DISPLAYTEXT\n" + "SDO_CS.MAKE_2D ( s.SHAPE, 4326 ), --POINT/LINE/POLYGON\n" + "ROUND ( s.SHAPE.SDO_POINT.y, 6 ), --LATITUDE\n" + "ROUND ( s.SHAPE.SDO_POINT.x, 6 ), --LONGITUDE\n" + "nrz.BEAT_ID, --BEAT\n" + "UPPER(s.POSTAL_CD), --ZIPCODE\n" + "UPPER(COALESCE(cityBoundaryMatches.CITY,c.NAME)), --CITY\n" + "UPPER(countyMatches.COUNTY), --COUNTY\n" + "UPPER(countyMatches.STATE), --STATE\n" + "UPPER(COALESCE(nd.DIVNAME,nsd.DIVNAME)), --DIVISION\n" + "UPPER(nrz.FIELD_OFF), --FIELD_OFFICE\n" + "nrz.AREA_ID, --AREA\n" + "NULL, --JURISDICTION\n" + "NULL, --MUNICIPALITY\n" + "NULL, --LOCATION_ID\n" + "UPPER(ITGIS.MAKE_NSPD_MILEPOST_SHORT(TRIM(s.PREFIX),TRIM(s.SUFFIX),s.MP)), --MILEPOST\n" + "UPPER(TRIM(s.PREFIX)), --MP_PREFIX\n" + "s.MP, --MP_NUMBER\n" + "UPPER(TRIM(s.SUFFIX)), --MP_SUFFIX\n" + "UPPER(s.NAME), --NAME\n" + "NULL, --CROSSING_DOT_NUM\n" + "NULL, --CROSSING_FULL_NAME\n" + "NULL --TRACK_SEGMENT_RANGE\n" + "FROM ITGIS.COMP_AEI_SCNR s\n" + "LEFT JOIN nsdMatches nsd\n" + "ON s.OBJECTID = nsd.OBJECTID\n" + "LEFT JOIN nrzMatches nrz\n" + "ON s.OBJECTID = nrz.OBJECTID\n" + "LEFT JOIN cityBoundaryMatches\n" + "ON s.OBJECTID = cityBoundaryMatches.OBJECTID\n" + "LEFT JOIN countyMatches\n" + "ON s.OBJECTID = countyMatches.OBJECTID\n" + "LEFT JOIN ITGIS.NSPD_LOCATIONS nl\n" + "ON nrz.BEAT_ID = nl.BEAT\n" + "LEFT JOIN ITGIS.NS_DIVISIONS nd\n" + "ON s.DIVCODE = nd.DIVCODE\n" + "LEFT JOIN NFSAPP.CONTROL nfsac\n" + "ON(ITGIS.MAKE_LINEID(s.DIVCODE, s.PREFIX, s.SUFFIX) = nfsac.LINE_ID AND s.MP BETWEEN nfsac.LMP AND nfsac.HMP)\n" + "CROSS JOIN PUBDATA.US_CITIES c\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_NN ( c.SHAPE, s.SHAPE, 'sdo_num_res = 1', 1 ) = 'TRUE'\n" + "--AND ROWNUM = 1\n" + "AND 1 = 1\n" + "]'\n" + "WHERE 1 = 1\n" + " AND XSCHEMA = 'ITGIS'\n" + " AND XTABLE = 'COMP_AEI_SCNR'\n" + " AND 1 = 1;\n\n" + "/* ITGIS.NSPD_LOCATIONS*/\n" + "UPDATE ITGIS.GLOBALSEARCHMASTER\n" + "SET QUERYTEXT =\n" + " q'[\n" + "WITH nsdMatches AS\n" + "(\n" + "SELECT l.OBJECTID,\n" + "nsd.DIVCODE,\n" + "nsd.DIVDESCR,\n" + "nsd.DIVABBR,\n" + "nsd.DIVNAME\n" + "FROM ITGIS.NSPD_LOCATIONS l\n" + "CROSS JOIN ITGIS.NS_DIVISIONS nsd\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_RELATE ( l.SHAPE, nsd.SHAPE, 'mask = anyinteract' ) = 'TRUE'\n" + "AND 1 = 1\n" + "),\n" + "nrzMatches AS\n" + "(\n" + "SELECT l.OBJECTID,\n" + "nrz.BEAT_ID,\n" + "nrz.AREA_ID,\n" + "nrz.FIELD_OFF,\n" + "nrz.COMMON_NAME NSPD_RESP_ZONE\n" + "FROM ITGIS.NSPD_LOCATIONS l\n" + "CROSS JOIN ITGIS.NSPD_RESP_ZONE nrz\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_RELATE ( l.SHAPE, nrz.SHAPE, 'mask = anyinteract' ) = 'TRUE'\n" + "AND 1 = 1\n" + "),\n" + "countyMatches AS\n" + "(\n" + "SELECT l.OBJECTID,\n" + "usc.STATE,\n" + "usc.COUNTY\n" + "FROM ITGIS.NSPD_LOCATIONS l\n" + "CROSS JOIN PUBDATA.US_COUNTIES usc\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_RELATE ( usc.SHAPE, l.SHAPE, 'mask = anyinteract' ) = 'TRUE'\n" + "AND 1 = 1\n" + "),\n" + "cityBoundaryMatches AS\n" + "(\n" + "SELECT l.OBJECTID,\n" + "uscb.NAME CITY\n" + "FROM ITGIS.NSPD_LOCATIONS l\n" + "CROSS JOIN PUBDATA.US_CITIES_BOUNDARIES uscb\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_RELATE ( uscb.SHAPE, l.SHAPE, 'mask = anyinteract' ) = 'TRUE'\n" + "AND 1 = 1\n" + ")\n" + "SELECT\n" + "***MASTERID***, --MASTERID\n" + "l.OBJECTID, --XUID\n" + "UPPER(l.OFFICEDESCRIPTION), --SEARCHTEXT\n" + "UPPER(l.ADDRESS), --DISPLAYTEXT\n" + "SDO_CS.MAKE_2D ( l.SHAPE, 4326 ), --POINT/LINE/POLYGON\n" + "ROUND ( l.SHAPE.SDO_POINT.y, 6 ), --LATITUDE\n" + "ROUND ( l.SHAPE.SDO_POINT.x, 6 ), --LONGITUDE\n" + "nrz.BEAT_ID, --BEAT\n" + "UPPER(l.POSTALCODE), --ZIPCODE\n" + "UPPER(COALESCE(l.CITY,cityBoundaryMatches.CITY,c.NAME)), --CITY\n" + "UPPER(countyMatches.COUNTY), --COUNTY\n" + "UPPER(COALESCE(l.STATE,countyMatches.STATE)), --STATE\n" + "UPPER(nsd.DIVNAME), --DIVISION\n" + "UPPER(l.OFFICEDESCRIPTION), --FIELD_OFFICE\n" + "nrz.AREA_ID, --AREA\n" + "NULL, --JURISDICTION\n" + "NULL, --MUNICIPALITY\n" + "NULL, --LOCATION_ID\n" + "NULL, --MILEPOST\n" + "NULL, --MP_PREFIX\n" + "NULL, --MP_NUMBER\n" + "NULL, --MP_SUFFIX\n" + "UPPER(l.OFFICEDESCRIPTION), --NAME\n" + "NULL, --CROSSING_DOT_NUM\n" + "NULL, --CROSSING_FULL_NAME\n" + "NULL --TRACK_SEGMENT_RANGE\n" + "FROM ITGIS.NSPD_LOCATIONS l\n" + "LEFT JOIN nsdMatches nsd\n" + "ON l.OBJECTID = nsd.OBJECTID\n" + "LEFT JOIN nrzMatches nrz\n" + "ON l.OBJECTID = nrz.OBJECTID\n" + "LEFT JOIN cityBoundaryMatches\n" + "ON l.OBJECTID = cityBoundaryMatches.OBJECTID\n" + "LEFT JOIN countyMatches\n" + "ON l.OBJECTID = countyMatches.OBJECTID\n" + "LEFT JOIN ITGIS.NSPD_LOCATIONS nl\n" + "ON nrz.BEAT_ID = nl.BEAT\n" + "--NO DIVCODE FIELD TO JOIN TO\n" + "CROSS JOIN PUBDATA.US_CITIES c\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_NN ( c.SHAPE, l.SHAPE, 'sdo_num_res = 1', 1 ) = 'TRUE'\n" + "--AND ROWNUM = 1\n" + "AND 1 = 1\n" + "]'\n" + "WHERE 1 = 1\n" + " AND XSCHEMA = 'ITGIS'\n" + " AND XTABLE = 'NSPD_LOCATIONS'\n" + " AND 1 = 1;\n\n" + "/* NSPD.NSPD_POI */\n" + "UPDATE ITGIS.GLOBALSEARCHMASTER\n" + "SET QUERYTEXT =\n" + "q'[\n" + "WITH nsdMatches AS\n" + "(\n" + "SELECT poi.ID,\n" + "nsd.DIVCODE,\n" + "nsd.DIVDESCR,\n" + "nsd.DIVABBR,\n" + "nsd.DIVNAME\n" + "FROM NSPD.NSPD_POI poi\n" + "CROSS JOIN ITGIS.NS_DIVISIONS nsd\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_RELATE ( poi.SHAPE, nsd.SHAPE, 'mask = anyinteract' ) = 'TRUE'\n" + "AND 1 = 1\n" + "),\n" + "nrzMatches AS\n" + "(\n" + "SELECT poi.ID,\n" + "nrz.BEAT_ID,\n" + "nrz.AREA_ID,\n" + "nrz.FIELD_OFF,\n" + "nrz.COMMON_NAME NSPD_RESP_ZONE\n" + "FROM NSPD.NSPD_POI poi\n" + "CROSS JOIN ITGIS.NSPD_RESP_ZONE nrz\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_RELATE ( poi.SHAPE, nrz.SHAPE, 'mask = anyinteract' ) = 'TRUE'\n" + "AND 1 = 1\n" + "),\n" + "countyMatches AS\n" + "(\n" + "SELECT poi.ID,\n" + "usc.STATE,\n" + "usc.COUNTY\n" + "FROM NSPD.NSPD_POI poi\n" + "CROSS JOIN PUBDATA.US_COUNTIES usc\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_RELATE ( usc.SHAPE, poi.SHAPE, 'mask = anyinteract' ) = 'TRUE'\n" + "AND 1 = 1\n" + "),\n" + "cityBoundaryMatches AS\n" + "(\n" + "SELECT poi.ID,\n" + "uscb.NAME CITY\n" + "FROM NSPD.NSPD_POI poi\n" + "CROSS JOIN PUBDATA.US_CITIES_BOUNDARIES uscb\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_RELATE ( uscb.SHAPE, poi.SHAPE, 'mask = anyinteract' ) = 'TRUE'\n" + "AND 1 = 1\n" + ")\n" + "SELECT\n" + "***MASTERID***, --MASTERID\n" + "poi.ID, --XUID\n" + "UPPER( poi.NAME), --SEARCHTEXT\n" + "UPPER( poi.ADDRESS), --DISPLAYTEXT\n" + "SDO_CS.MAKE_2D ( poi.SHAPE, 4326 ), --POINT/LINE/POLYGON\n" + "ROUND ( poi.SHAPE.SDO_POINT.y, 6 ), --LATITUDE\n" + "ROUND ( poi.SHAPE.SDO_POINT.x, 6 ), --LONGITUDE\n" + "nrz.BEAT_ID, --BEAT\n" + "NULL, --ZIPCODE\n" + "UPPER(COALESCE(CAST(poi.CITY AS NVARCHAR2(100)),cityBoundaryMatches.CITY,c.NAME)), --CITY\n" + "UPPER(COALESCE(CAST(poi.COUNTY AS NVARCHAR2(100)),countyMatches.COUNTY)), --COUNTY\n" + "UPPER(COALESCE(CAST(poi.STATE AS NVARCHAR2(100)),countyMatches.STATE)), --STATE\n" + "UPPER(nsd.DIVNAME), --DIVISION\n" + "UPPER(nrz.FIELD_OFF), --FIELD_OFFICE\n" + "nrz.AREA_ID, --AREA\n" + "NULL, --JURISDICTION\n" + "NULL, --MUNICIPALITY\n" + "NULL, --LOCATION_ID\n" + "NULL, --MILEPOST\n" + "NULL, --MP_PREFIX\n" + "NULL, --MP_NUMBER\n" + "NULL, --MP_SUFFIX\n" + "UPPER( poi.NAME), --NAME\n" + "NULL, --CROSSING_DOT_NUM\n" + "NULL, --CROSSING_FULL_NAME\n" + "NULL --TRACK_SEGMENT_RANGE\n" + "FROM NSPD.NSPD_POI poi\n" + "LEFT JOIN nsdMatches nsd\n" + "ON poi.ID = nsd.ID\n" + "LEFT JOIN nrzMatches nrz\n" + "ON poi.ID = nrz.ID\n" + "LEFT JOIN cityBoundaryMatches\n" + "ON poi.ID = cityBoundaryMatches.ID\n" + "LEFT JOIN countyMatches\n" + "ON poi.ID = countyMatches.ID\n" + "LEFT JOIN ITGIS.NSPD_LOCATIONS nl\n" + "ON nrz.BEAT_ID = nl.BEAT\n" + "--NO DIVCODE FIELD TO JOIN TO\n" + "CROSS JOIN PUBDATA.US_CITIES c\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_NN ( c.SHAPE, poi.SHAPE, 'sdo_num_res = 1', 1 ) = 'TRUE'\n" + "--AND ROWNUM = 1\n" + "AND 1 = 1\n" + "]'\n" + "WHERE 1 = 1\n" + "AND XSCHEMA = 'NSPD'\n" + "AND XTABLE = 'NSPD_POI'\n" + "AND 1 = 1;\n\n" + "/* ITGIS.ITGIS_MP100S */\n" + "UPDATE ITGIS.GLOBALSEARCHMASTER\n" + "SET QUERYTEXT =\n" + "q'[\n" + "WITH nsdMatches AS\n" + "(\n" + "SELECT mp.OBJECTID,\n" + "nsd.DIVCODE,\n" + "nsd.DIVDESCR,\n" + "nsd.DIVABBR,\n" + "nsd.DIVNAME\n" + "FROM ITGIS.ITGIS_MP100S mp\n" + "CROSS JOIN ITGIS.NS_DIVISIONS nsd\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_RELATE ( mp.SHAPE, nsd.SHAPE, 'mask = anyinteract' ) = 'TRUE'\n" + "AND 1 = 1\n" + "),\n" + "nrzMatches AS\n" + "(\n" + "SELECT mp.OBJECTID,\n" + "nrz.BEAT_ID,\n" + "nrz.AREA_ID,\n" + "nrz.FIELD_OFF,\n" + "nrz.COMMON_NAME NSPD_RESP_ZONE\n" + "FROM ITGIS.ITGIS_MP100S mp\n" + "CROSS JOIN ITGIS.NSPD_RESP_ZONE nrz\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_RELATE ( mp.SHAPE, nrz.SHAPE, 'mask = anyinteract' ) = 'TRUE'\n" + "AND 1 = 1\n" + "),\n" + "countyMatches AS\n" + "(\n" + "SELECT mp.OBJECTID,\n" + "usc.STATE,\n" + "usc.COUNTY\n" + "FROM ITGIS.ITGIS_MP100S mp\n" + "CROSS JOIN PUBDATA.US_COUNTIES usc\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_RELATE ( usc.SHAPE, mp.SHAPE, 'mask = anyinteract' ) = 'TRUE'\n" + "AND 1 = 1\n" + "),\n" + "cityBoundaryMatches AS\n" + "(\n" + "SELECT mp.OBJECTID,\n" + "uscb.NAME CITY\n" + "FROM ITGIS.ITGIS_MP100S mp\n" + "CROSS JOIN PUBDATA.US_CITIES_BOUNDARIES uscb\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_RELATE ( uscb.SHAPE, mp.SHAPE, 'mask = anyinteract' ) = 'TRUE'\n" + "AND 1 = 1\n" + ")\n" + "SELECT\n" + "***MASTERID***, --MASTERID\n" + "mp.OBJECTID, --XUID\n" + "UPPER(ITGIS.MAKE_NSPD_MP_DB_PARTS(mp.DIVCODE,TRIM(mp.PREFIX),TRIM(mp.SUFFIX),TRIM(mp.MP))), --SEARCHTEXT\n" + "UPPER(ITGIS.MAKE_NSPD_MP_DB_PARTS(mp.DIVCODE,TRIM(mp.PREFIX),TRIM(mp.SUFFIX),TRIM(mp.MP))), --DISPLAYTEXT\n" + "SDO_CS.MAKE_2D ( mp.SHAPE, 4326 ), --POINT/LINE/POLYGON\n" + "ROUND ( mp.SHAPE.SDO_POINT.y, 6 ), --LATITUDE\n" + "ROUND ( mp.SHAPE.SDO_POINT.x, 6 ), --LONGITUDE\n" + "nrz.BEAT_ID, --BEAT\n" + "NULL, --ZIPCODE\n" + "UPPER(COALESCE(cityBoundaryMatches.CITY,c.NAME)), --CITY\n" + "UPPER(countyMatches.COUNTY), --COUNTY\n" + "UPPER(countyMatches.STATE), --STATE\n" + "UPPER(COALESCE(nd.DIVNAME,nsd.DIVNAME)), --DIVISION\n" + "UPPER(nrz.FIELD_OFF), --FIELD_OFFICE\n" + "nrz.AREA_ID, --AREA\n" + "NULL, --JURISDICTION\n" + "NULL, --MUNICIPALITY\n" + "NULL, --LOCATION_ID\n" + "UPPER(ITGIS.MAKE_NSPD_MILEPOST_SHORT(TRIM(mp.PREFIX),TRIM(mp.SUFFIX),mp.MP)), --MILEPOST\n" + "UPPER(TRIM(mp.PREFIX)), --MP_PREFIX\n" + "mp.MP, --MP_NUMBER\n" + "UPPER(TRIM(mp.SUFFIX)), --MP_SUFFIX\n" + "UPPER(ITGIS.MAKE_NSPD_MP_DB_PARTS(mp.DIVCODE,TRIM(mp.PREFIX),TRIM(mp.SUFFIX),TRIM(mp.MP))), --NAME\n" + "NULL, --CROSSING_DOT_NUM\n" + "NULL, --CROSSING_FULL_NAME\n" + "NULL --TRACK_SEGMENT_RANGE\n" + "FROM ITGIS.ITGIS_MP100S mp\n" + "LEFT JOIN NFSAPP.CONTROL nfsac\n" + "ON\n" + "(\n" + "ITGIS.MAKE_LINEID ( mp.DIVCODE, TRIM ( mp.PREFIX ), TRIM ( mp.SUFFIX ) ) = nfsac.LINE_ID\n" + "AND mp.MP BETWEEN nfsac.LMP AND nfsac.HMP\n" + ")\n" + "LEFT JOIN nrzMatches nrz\n" + "ON mp.OBJECTID = nrz.OBJECTID\n" + "LEFT JOIN nsdMatches nsd\n" + "ON mp.OBJECTID = nsd.OBJECTID\n" + "LEFT JOIN cityBoundaryMatches\n" + "ON mp.OBJECTID = cityBoundaryMatches.OBJECTID\n" + "LEFT JOIN countyMatches\n" + "ON mp.OBJECTID = countyMatches.OBJECTID\n" + "LEFT JOIN ITGIS.NSPD_LOCATIONS nl\n" + "ON nrz.BEAT_ID = nl.BEAT\n" + "LEFT JOIN ITGIS.NS_DIVISIONS nd\n" + "ON mp.DIVCODE = nd.DIVCODE\n" + "CROSS JOIN PUBDATA.US_CITIES c\n" + "WHERE 1 = 1\n" + "AND MDSYS.SDO_NN ( c.SHAPE, mp.SHAPE, 'sdo_num_res = 1', 1 ) = 'TRUE'\n" + "--AND ROWNUM = 1\n" + "AND 1 = 1\n" + "]'\n" + "WHERE 1 = 1\n" + "AND XSCHEMA = 'ITGIS'\n" + "AND XTABLE = 'ITGIS_MP100S'\n" + "AND 1 = 1;\n\n" + "-- /* template for any additional data */\n" + "-- UPDATE ITGIS.GLOBALSEARCHMASTER\n" + "-- SET QUERYTEXT =\n" + "-- q'[\n\n" + "-- ]'\n" + "-- WHERE 1 = 1\n" + "-- AND XSCHEMA = ''\n" + "-- AND XTABLE = ''\n" + "-- AND 1 = 1;\n\n" + "--------------------------------------------------------\n" + "-- CONTROLS WHICH TABLE GETS USED IN INSERT/SELECT\n" + "--------------------------------------------------------\n\n" + "INSERT\n" + "INTO ITGIS.GLOBALSEARCHSWITCH\n" + " (\n" + " TABLESWITCH,\n" + " PROCEDUREENABLED\n" + " )\n" + " VALUES\n" + " ( \n" + " -1,\n" + " 'Y'\n" + " ) ;\n\n" + "INSERT\n" + "INTO ITGIS.GLOBALSEARCHTABLE\n" + " (\n" + " TABLESWITCH,\n" + " GLOBALSEARCHTABLE\n" + " )\n" + " VALUES\n" + " ( \n" + " -1,\n" + " 'GLOBALSEARCHDATA_A'\n" + " ) ;\n\n" + "INSERT\n" + "INTO ITGIS.GLOBALSEARCHTABLE\n" + " (\n" + " TABLESWITCH,\n" + " GLOBALSEARCHTABLE\n" + " )\n" + " VALUES\n" + " ( \n" + " 1,\n" + " 'GLOBALSEARCHDATA_B'\n" + " ) ;\n\n" + "--------------------------------------------------------\n" + "-- COMMIT\n" + "--------------------------------------------------------\n\n" + "COMMIT;\n\n" + "--------------------------------------------------------\n" + "-- EXECUTE PROCEDURE TO GENERATE DATA\n" + "--------------------------------------------------------\n\n" + "--EXECUTE NSPD.FILLGLOBALSEARCHTABLE_PR;\n"; 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