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

# coding=utf8 # the above tag defines encoding for this document and is for Python 2.x compatibility import re regex = r"(LEFT|RIGHT|CROSS)\s{0,}(INNER|OUTER){0,}\s{0,}JOIN.{0,}$|FROM.{0,}$" test_str = ("--------------------------------------------------------\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") matches = re.finditer(regex, test_str, re.MULTILINE) for matchNum, match in enumerate(matches, start=1): print ("Match {matchNum} was found at {start}-{end}: {match}".format(matchNum = matchNum, start = match.start(), end = match.end(), match = match.group())) for groupNum in range(0, len(match.groups())): groupNum = groupNum + 1 print ("Group {groupNum} found at {start}-{end}: {group}".format(groupNum = groupNum, start = match.start(groupNum), end = match.end(groupNum), group = match.group(groupNum))) # Note: for Python 2.7 compatibility, use ur"" to prefix the regex and u"" to prefix the test string and substitution.

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 Python, please visit: https://docs.python.org/3/library/re.html