Db2 11 Regular Expressions.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='top'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "# Db2 11 Regular Expressions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "Db2 11.1 introduced extended support for regular\n",
    "expressions. Regular expressions allow you to do very complex\n",
    "pattern matching in character strings. Normal SQL LIKE\n",
    "searches are limited to very specific patterns, but Regular\n",
    "expression have a rich syntax that gives you much more\n",
    "flexibility in searching.\n",
    "\n",
    "Set up the connection to the database and Db2 command extensions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "hideCode": false,
    "hideOutput": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DB2 Extensions Loaded.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<style>\n",
       "  table {margin-left: 0 !important; text-align: left;}\n",
       "</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%run db2.ipynb"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Table of Contents\n",
    "\n",
    "* [Sample Table Setup](#sample)\n",
    "* [Regular Expression Commands](#regular)\n",
    "* [Regular Expression Flags](#flags)\n",
    "* [Regular Expression Search Patterns](#patterns)\n",
    "* [Anchoring Patterns in a Search](#anchor)\n",
    "* [Matching Patterns Across Lines](#multiple)\n",
    "* [Logical OR Operator](#or)\n",
    "* [Combining Patterns](#combine)\n",
    "* [Matching Character Types](#chars)\n",
    "* [Special Patterns](#special)\n",
    "* [Negating Patterns](#negate)\n",
    "* [Capturing Parenthesis](#capture)\n",
    "* [Performance Considerations](#performance)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='sample'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "## Sample Table Setup\n",
    "\n",
    "The following SQL will create a new table in the SAMPLE\n",
    "database (or whatever database you are currently connected\n",
    "to). This table represents all of the stations of the London\n",
    "Underground Central line (existing stations only, not\n",
    "historical ones!). This table will be used for all of the\n",
    "examples within this section."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connection successful.\n"
     ]
    }
   ],
   "source": [
    "%%sql -q\n",
    "DROP TABLE CENTRAL_LINE;\n",
    "\n",
    "CREATE TABLE CENTRAL_LINE\n",
    "  (\n",
    "  STATION_NO INTEGER GENERATED ALWAYS AS IDENTITY,\n",
    "  STATION VARCHAR(31),\n",
    "  UPPER_STATION VARCHAR(31) GENERATED ALWAYS AS (UCASE(STATION))\n",
    "  )\n",
    ";\n",
    "\n",
    "INSERT INTO CENTRAL_LINE(STATION)\n",
    "  VALUES 'West Ruislip','Ruislip Gardens','South Ruislip','Northolt','Greenford',\n",
    "         'Perivale','Hanger Lane','Ealing Broadway','West Acton','North Acton',\n",
    "         'East Acton','White City','Shepherd''s Bush','Holland Park','Notting Hill Gate',\n",
    "         'Queensway','Lancaster Gate','Marble Arch','Bond Street','Oxford Circus',\n",
    "         'Tottenham Court Road','Holborn','Chancery Lane','St. Paul''s','Bank',\n",
    "         'Liverpool Street','Bethnal Green','Mile End','Stratford','Leyton',\n",
    "         'Leytonstone','Wanstead','Redbridge','Gants Hill','Newbury Park',\n",
    "         'Barkingside','Fairlop','Hainault','Grange Hill','Chigwell',\n",
    "         'Roding Valley','Snaresbrook','South Woodford','Woodford','Buckhurst Hill',\n",
    "         'Loughton','Debden','Theydon Bois','Epping'\n",
    ";"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='regular'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "# Regular Expression Commands\n",
    "\n",
    "\n",
    "There are six regular expression functions within DB2\n",
    "including:\n",
    "\n",
    "- **REGEXP_COUNT** - Returns a count of the number of times that a regular expression pattern is matched in a string.\n",
    "- **REGEXP_EXTRACT** - Returns one occurrence of a substring of a string that matches the regular expression pattern.\n",
    "- **REGEXP_INSTR** - Returns the starting or ending position of the matched substring, depending on the value of the return option argument.\n",
    "- **REGEXP_LIKE** - Returns a Boolean value indicating if the regular expression pattern is found in a string. The function can be used only where a predicate is supported.\n",
    "- **REGEXP_MATCH_COUNT** - Returns a count of the number of times that a regular expression pattern is matched in a string.\n",
    "- **REGEXP_REPLACE** - Returns a modified version of the source string where occurrences of the regular expression pattern found in the source string are replaced with the specified replacement string.\n",
    "- **REGEXP_SUBSTR** - Returns one occurrence of a substring of a string that matches the regular expression pattern.\n",
    "\n",
    "Each one of these functions follows a similar calling\n",
    "sequence:\n",
    "<pre> \n",
    " REGEXP_FUNCTION(source, pattern, flags, start_pos, codeunits)\n",
    "</pre>\n",
    "\n",
    "The arguments to the function are:\n",
    "\n",
    "- **Source** - string to be searched\n",
    "- **Pattern** - the regular expression that contains what we are searching for\n",
    "- **Flag** - settings that control how matching is done\n",
    "- **Start_pos** - where to start in the string\n",
    "- **Codeunits** - which type unit of measurement start_pos refers to (for Unicode)\n",
    "\n",
    "The source can be any valid Db2 string including CHAR,\n",
    "VARCHAR, CLOB, etc. Start_pos is the location in the source\n",
    "string that you want to start searching from, and codeunits\n",
    "tells Db2 whether to treat the start_pos as an absolute\n",
    "location (think byte location) or a character location which\n",
    "takes into account the unicode size of the character string.\n",
    "\n",
    "Codeunits can be specified as CODEUNITS16, CODEUNITS32, or\n",
    "OCTETS. CODEUNITS16 specifies that start is expressed in\n",
    "16-bit UTF-16 code units. CODEUNITS32 specifies that start\n",
    "is expressed in 32-bit UTF-32 code units. OCTETS specifies\n",
    "that start is expressed in bytes.\n",
    "\n",
    "Pattern and flag values are complex and so are discussed in\n",
    "the following sections."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='flags'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "## Regular Express Flag Values\n",
    " \n",
    "Regular expression functions have a flag specification that\n",
    "can be used to change the behavior of the search. There are\n",
    "six possible flags that can be specified as part of the\n",
    "REGEXP command:\n",
    "\n",
    "|Flag  |Purpose\n",
    "|:----:|:--------------------\n",
    "|**c** | Specifies that matching is case-sensitive (the default value)\n",
    "|**i** | Specifies that matching is case insensitive\n",
    "|**m** | Specifies that the input data can contain more than one line. By default, the '^' in a pattern matches only the start of the input string; the '\\$' in a pattern matches only the end of the input string. If this flag is set, \"^\" and \"\\$\" also matches at the start and end of each line within the input string.\n",
    "|**n** | Specifies that the '.' character in a pattern matches a line terminator in the input string. By default, the '.' character in a pattern does not match a line terminator. A carriage-return and line-feed pair in the input string behaves as a single-line terminator, and matches a single \".\" in a pattern.\n",
    "|**s** | Specifies that the '.' character in a pattern matches a line terminator in the input string. This value is a synonym for the 'n' value.\n",
    "|**x** | Specifies that white space characters in a pattern are ignored, unless escaped."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='patterns'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "## Regular Expression Search Patterns\n",
    "\n",
    "Regular expressions use certain characters to represent what\n",
    "is matched in a string. The simplest pattern is a string by\n",
    "itself."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STATION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>West Ruislip</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Ruislip Gardens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>South Ruislip</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           STATION\n",
       "0     West Ruislip\n",
       "1  Ruislip Gardens\n",
       "2    South Ruislip"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT STATION FROM CENTRAL_LINE\n",
    "  WHERE REGEXP_LIKE(STATION,'Ruislip')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "The pattern 'Ruislip' will look for a match of Ruislip\n",
    "within the STATION column. Note that this pattern will also\n",
    "match 'West Ruislip' or 'Ruislip Gardens' since we placed no\n",
    "restriction on where the pattern can be found in the string.\n",
    "The match will also be exact (case matters). This type of\n",
    "search would be equivalent to using the SQL LIKE statement:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STATION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>West Ruislip</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Ruislip Gardens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>South Ruislip</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           STATION\n",
       "0     West Ruislip\n",
       "1  Ruislip Gardens\n",
       "2    South Ruislip"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT STATION FROM CENTRAL_LINE\n",
    "  WHERE STATION LIKE '%Ruislip%'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "If you didn't place the % at the beginning of the LIKE\n",
    "string, only the stations that start with Ruislip would be\n",
    "found."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STATION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Ruislip Gardens</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           STATION\n",
       "0  Ruislip Gardens"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT STATION FROM CENTRAL_LINE\n",
    "  WHERE STATION LIKE 'Ruislip%'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "If you want to match Ruislip with upper or lower case being\n",
    "ignored, you would add the 'i' flag as part of the\n",
    "REGEXP_LIKE (or any REGEXP function)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STATION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>West Ruislip</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Ruislip Gardens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>South Ruislip</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           STATION\n",
       "0     West Ruislip\n",
       "1  Ruislip Gardens\n",
       "2    South Ruislip"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT STATION FROM CENTRAL_LINE\n",
    "  WHERE REGEXP_LIKE(STATION,'RUISLIP','i')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='anchor'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "## Anchoring Patterns in a Search\n",
    " \n",
    "By default a pattern will be matched anywhere in a string.\n",
    "Our previous example showed how Ruislip could be found\n",
    "anywhere in a string. To force a match to start at the\n",
    "beginning of a string, the carat symbol ^ can be used to\n",
    "force the match to occur at the beginning of a string."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STATION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Ruislip Gardens</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           STATION\n",
       "0  Ruislip Gardens"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "  SELECT STATION FROM CENTRAL_LINE\n",
    "    WHERE REGEXP_LIKE(STATION,'^Ruislip')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "To match a pattern at the end of the string, the dollar sign\n",
    "\\$ can be used."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STATION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>West Ruislip</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>South Ruislip</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         STATION\n",
       "0   West Ruislip\n",
       "1  South Ruislip"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "  SELECT STATION FROM CENTRAL_LINE\n",
    "    WHERE REGEXP_LIKE(STATION,'Ruislip$')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "To force an exact match with a string you would use both the\n",
    "beginning and end anchors."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STATION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Leyton</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  STATION\n",
       "0  Leyton"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql \n",
    "  SELECT STATION FROM CENTRAL_LINE\n",
    "    WHERE REGEXP_LIKE(STATION,'^Leyton$')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "Note that if we didn't use the end anchor, we are going to\n",
    "get more than one result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STATION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Leyton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Leytonstone</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       STATION\n",
       "0       Leyton\n",
       "1  Leytonstone"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql \n",
    "  SELECT STATION FROM CENTRAL_LINE\n",
    "    WHERE REGEXP_LIKE(STATION,'^Leyton');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='multiple'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "## Matching patterns across multiple lines\n",
    "\n",
    "So far the examples have dealt with strings that do not\n",
    "contain newline characters (or carriage feeds). In some\n",
    "applications, data from an input panel may include multiple\n",
    "lines which may contain hard line feeds. What this means is\n",
    "that there are actually multiple lines in the data, but from\n",
    "a database perspective, there is only one line in the\n",
    "VARCHAR field. You can modify the behavior of the Regular\n",
    "Expression search by instructing it to honor the CRLF\n",
    "characters as line delimiters.\n",
    "\n",
    "The following SQL will insert a single line with multiple\n",
    "CRLF characters in it to simulate a multi-line text string."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "collapsed": true,
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [],
   "source": [
    "%%sql -q\n",
    "DROP TABLE LONGLINE;\n",
    "\n",
    "CREATE TABLE LONGLINE (NAME VARCHAR(255));\n",
    "\n",
    "INSERT INTO LONGLINE\n",
    "  VALUES 'George' || CHR(10) || 'Katrina';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "Searching for Katrina at the beginning and end of string\n",
    "doesn't work."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   1\n",
       "0  0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT COUNT(*) FROM LONGLINE\n",
    "  WHERE REGEXP_LIKE(NAME,'^Katrina$')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "We can override the regular expression search by telling it\n",
    "to treat each NL/CRLF as the end of a string within a\n",
    "string."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   1\n",
       "0  1"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT COUNT(*) FROM LONGLINE\n",
    "  WHERE REGEXP_LIKE(NAME,'^Katrina$','m')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='or'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "## Logical OR Operator\n",
    " \n",
    "Regular expressions can match more than one pattern. The OR\n",
    "operator (|) is used to define alternative patterns that can\n",
    "match in a string. The following example searches for\n",
    "stations that have \"ing\" in their name as well as \"hill\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STATION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Ealing Broadway</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Notting Hill Gate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Queensway</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Barkingside</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Roding Valley</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Epping</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             STATION\n",
       "0    Ealing Broadway\n",
       "1  Notting Hill Gate\n",
       "2          Queensway\n",
       "3        Barkingside\n",
       "4      Roding Valley\n",
       "5             Epping"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT STATION FROM CENTRAL_LINE\n",
    "  WHERE REGEXP_LIKE(STATION,'way|ing')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "Some things to be aware of when creating the search pattern.\n",
    "Spaces in the patterns themselves are significant. If the\n",
    "previous search pattern had a space in one of the words, it\n",
    "would not find it (unless of course there was a space in the\n",
    "station name)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STATION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Ealing Broadway</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Queensway</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           STATION\n",
       "0  Ealing Broadway\n",
       "1        Queensway"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT STATION FROM CENTRAL_LINE\n",
    "  WHERE REGEXP_LIKE(STATION,'way| ing') "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "Using the \"x\" flag will ignore blanks in your pattern, so\n",
    "this would fix issues that we have in the previous example."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STATION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Ealing Broadway</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Notting Hill Gate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Queensway</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Barkingside</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Roding Valley</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Epping</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             STATION\n",
       "0    Ealing Broadway\n",
       "1  Notting Hill Gate\n",
       "2          Queensway\n",
       "3        Barkingside\n",
       "4      Roding Valley\n",
       "5             Epping"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT STATION FROM CENTRAL_LINE\n",
    "  WHERE REGEXP_LIKE(STATION,'way| ing','x')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "Brackets can be used to make it clear what the pattern is\n",
    "that you are searching for and avoid the problem of having\n",
    "blanks in the expression. Brackets do have a specific usage\n",
    "in regular expressions, but here we are using it only to\n",
    "separate the two search strings."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STATION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Ealing Broadway</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Notting Hill Gate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Queensway</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Barkingside</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Roding Valley</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Epping</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             STATION\n",
       "0    Ealing Broadway\n",
       "1  Notting Hill Gate\n",
       "2          Queensway\n",
       "3        Barkingside\n",
       "4      Roding Valley\n",
       "5             Epping"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT STATION FROM CENTRAL_LINE\n",
    "  WHERE REGEXP_LIKE(STATION,'(way)|(ing)')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='combine'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "## Combining Patterns \n",
    " \n",
    "As we found out in the previous section, there is an OR\n",
    "operator that you can use to select between two patterns.\n",
    "How do you request that multiple patterns be present? First\n",
    "we must understand how matching occurs when we have multiple\n",
    "strings that need to be matched that have an unknown number\n",
    "of characters between them.\n",
    "\n",
    "For instance, how do we create a pattern that looks for\n",
    "\"ing\" followed by \"way\" somewhere in the string? Regular\n",
    "expression recognize the \".\" (period) character as matching\n",
    "anything. Following the pattern you can add a modifier that\n",
    "specifies how many times you want the pattern matched:\n",
    "\n",
    "- **\\*** - Match zero or more times\n",
    "- **?** - Match zero or one times\n",
    "- **+** - Match one or more times\n",
    "- **{m}** - Match exactly m times\n",
    "- **{m,}** - Match as least a minimum of m times\n",
    "- **{m,n}** - Match at least a minimum of m times and no more than n times\n",
    "\n",
    "The following regular expression searches for a pattern with\n",
    "\"ing\" followed by any characters and then \"way\".\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STATION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Ealing Broadway</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Queensway</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           STATION\n",
       "0  Ealing Broadway\n",
       "1        Queensway"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT STATION FROM CENTRAL_LINE\n",
    "  WHERE REGEXP_LIKE(STATION,'(ing)*.(way)')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "The previous answer gave you two results (Ealing Broadway\n",
    "and Queensway). Why two? The reason is that we used the \\* in\n",
    "the wrong place (a single character in a wrong place can\n",
    "result in very different results!). What we really needed to\n",
    "do was place a .\\* after the (ing) to match \"ing\" and then\n",
    "any characters, before matching \"way\". What our query did\n",
    "above was match 0 or more occurences of \"ing\", followed by\n",
    "any character, and then match \"way\". Here is the correct\n",
    "query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STATION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Ealing Broadway</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           STATION\n",
       "0  Ealing Broadway"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT STATION FROM CENTRAL_LINE\n",
    "  WHERE REGEXP_LIKE(STATION,'(ing).*(way)')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "Finding at least one occurrence of a pattern requires the\n",
    "use of the + operator, or the bracket operators. This\n",
    "example locates at least one occurrence of the \"an\" string\n",
    "in station names."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STATION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Hanger Lane</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Holland Park</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lancaster Gate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Chancery Lane</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bank</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Wanstead</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Gants Hill</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Grange Hill</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          STATION\n",
       "0     Hanger Lane\n",
       "1    Holland Park\n",
       "2  Lancaster Gate\n",
       "3   Chancery Lane\n",
       "4            Bank\n",
       "5        Wanstead\n",
       "6      Gants Hill\n",
       "7     Grange Hill"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT STATION FROM CENTRAL_LINE\n",
    "  WHERE REGEXP_LIKE(STATION,'(an)+') "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "If we want to find an exact number of occurrences, we need\n",
    "to use the {} notation to tell the regular expression\n",
    "matcher how many we want to find. The syntax of the {}\n",
    "match is:\n",
    "\n",
    "- **{m}** - Match exactly m times\n",
    "- **{m,}** - Match as least a minimum of m times\n",
    "- **{m,n}** - Match at least a minimum of m times and no more than n times\n",
    "\n",
    "So the \"+\" symbol is equivalent to the following regular\n",
    "expression using the {} syntax."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STATION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Hanger Lane</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Holland Park</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lancaster Gate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Chancery Lane</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bank</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Wanstead</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Gants Hill</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Grange Hill</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          STATION\n",
       "0     Hanger Lane\n",
       "1    Holland Park\n",
       "2  Lancaster Gate\n",
       "3   Chancery Lane\n",
       "4            Bank\n",
       "5        Wanstead\n",
       "6      Gants Hill\n",
       "7     Grange Hill"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT STATION FROM CENTRAL_LINE\n",
    "  WHERE REGEXP_LIKE(STATION,'(an){1,}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "If we want to match exactly 2 'an' patterns in a string, we\n",
    "would think that changing the expression to {2} would work."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STATION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [STATION]\n",
       "Index: []"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT STATION FROM CENTRAL_LINE\n",
    "  WHERE REGEXP_LIKE(STATION,'(an){2}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "Sadly, we get no results! This would appear to be the wrong\n",
    "result, but it's because we got lucky with our first search!\n",
    "The best way to figure out what we matched in the original\n",
    "query is to use the REGEXP_EXTRACT and REGEXP_INSTR\n",
    "functions.\n",
    "\n",
    "- **REGEXP_EXTRACT** - Returns one occurrence of a substring of a string that matches the regular expression pattern.\n",
    "- **REGEXP_INSTR** - Returns the starting or ending position of the matched substring, depending on the value of the return option argument.\n",
    "\n",
    "The following SQL gives us a clue to what was found with the\n",
    "(an) pattern."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STATION</th>\n",
       "      <th>LOCATION</th>\n",
       "      <th>EXTRACT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Hanger Lane</td>\n",
       "      <td>2</td>\n",
       "      <td>an</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Holland Park</td>\n",
       "      <td>5</td>\n",
       "      <td>an</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lancaster Gate</td>\n",
       "      <td>2</td>\n",
       "      <td>an</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Chancery Lane</td>\n",
       "      <td>3</td>\n",
       "      <td>an</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bank</td>\n",
       "      <td>2</td>\n",
       "      <td>an</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Wanstead</td>\n",
       "      <td>2</td>\n",
       "      <td>an</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Gants Hill</td>\n",
       "      <td>2</td>\n",
       "      <td>an</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Grange Hill</td>\n",
       "      <td>3</td>\n",
       "      <td>an</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          STATION  LOCATION EXTRACT\n",
       "0     Hanger Lane         2      an\n",
       "1    Holland Park         5      an\n",
       "2  Lancaster Gate         2      an\n",
       "3   Chancery Lane         3      an\n",
       "4            Bank         2      an\n",
       "5        Wanstead         2      an\n",
       "6      Gants Hill         2      an\n",
       "7     Grange Hill         3      an"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT STATION,\n",
    "   REGEXP_INSTR(STATION,'(an)') AS LOCATION,\n",
    "   REGEXP_EXTRACT(STATION,'(an)') AS EXTRACT\n",
    "FROM CENTRAL_LINE\n",
    "   WHERE REGEXP_LIKE(STATION,'(an)')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "What you should see in the previous result is the location\n",
    "where the \"an\" pattern was found in the string. Note that\n",
    "all we matched was the \"an\" pattern, nothing else. So why\n",
    "can't I find two \"an\" patterns in the string? The reason is\n",
    "that (an){2} means \"an\" followed by another \"an\"! We didn't\n",
    "tell the pattern to match anything else! What we need to do\n",
    "is modify the pattern to say that it can match \"an\" followed\n",
    "by anything else. The pattern needs to be modifed to (an).*\n",
    "where the \".*\" means any character following the \"an\".\n",
    "\n",
    "In order to tell the regular expression function to use this\n",
    "entire pattern (an).\\* twice, we need to place brackets\n",
    "around it as well. The final pattern is ((an).\\*){2}."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STATION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Hanger Lane</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Chancery Lane</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         STATION\n",
       "0    Hanger Lane\n",
       "1  Chancery Lane"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT STATION FROM CENTRAL_LINE\n",
    "  WHERE REGEXP_LIKE(STATION,'((an).*){2}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "You should find that two stations match the pattern. The\n",
    "following SQL shows which pattern is matched first in the\n",
    "STATIONS names."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STATION</th>\n",
       "      <th>LOCATION</th>\n",
       "      <th>EXTRACT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Hanger Lane</td>\n",
       "      <td>2</td>\n",
       "      <td>anger Lane</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Chancery Lane</td>\n",
       "      <td>3</td>\n",
       "      <td>ancery Lane</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         STATION  LOCATION      EXTRACT\n",
       "0    Hanger Lane         2   anger Lane\n",
       "1  Chancery Lane         3  ancery Lane"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT STATION,\n",
    "   REGEXP_INSTR(STATION,'((an).*){2}') AS LOCATION,\n",
    "   REGEXP_EXTRACT(STATION,'((an).*){2}') AS EXTRACT\n",
    "FROM CENTRAL_LINE\n",
    "   WHERE REGEXP_LIKE(STATION,'((an).*){2}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='chars'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "## Matching character types \n",
    " \n",
    "Aside from matching entire strings, you can also use regular\n",
    "expression to look for patterns of characters. The simplest\n",
    "matching pattern is the period (.) which matches any\n",
    "character. Matching a string of arbitrary length is the\n",
    "pattern \".*\". The \"+\" and \"?\" characters can also be used to\n",
    "modify how many characters you want matched.\n",
    "\n",
    "What about situations where you want to check for certain\n",
    "patterns or characters in a string? A good example would be\n",
    "a social security number, or credit card number. There are\n",
    "certain patterns that you would find for these objects.\n",
    "Assume we have a social security number in the format\n",
    "xxx-xx-xxxx. It is possible to create a regular expression\n",
    "that would return true if the SSN matched the pattern above\n",
    "(it doesn't tell you if the SSN itself is valid, only that\n",
    "it has the proper format).\n",
    "\n",
    "Regular expressions allow you to create a list of characters\n",
    "that need to be matched in something called a bracket\n",
    "expression. A bracket expression has the format:\n",
    "\n",
    "<pre> \n",
    "[a-z] [A-Z] [0-9] [a-zA-z]\n",
    "</pre>\n",
    "\n",
    "The examples above represent the following search patterns:\n",
    "\n",
    "- **[a-z]** - match any series of lowercase characters between a and z\n",
    "- **[A-Z]** - match any series of uppercase characters between A and Z\n",
    "- **[0-9]** - match any valid digits\n",
    "- **[a-zA-Z]** - match any lower- or uppercase letters\n",
    "\n",
    "You can also enumerate all of the characters you want to\n",
    "match by listing them between the brackets like\n",
    "[abcdefghikjlmnopqrstuvwxyz]. The short form a-z is easier\n",
    "to read and less prone to typing errors!\n",
    "\n",
    "The following example checks for station names that start\n",
    "with the letter P-R."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STATION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Ruislip Gardens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Perivale</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Queensway</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Redbridge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Roding Valley</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           STATION\n",
       "0  Ruislip Gardens\n",
       "1         Perivale\n",
       "2        Queensway\n",
       "3        Redbridge\n",
       "4    Roding Valley"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT STATION FROM CENTRAL_LINE\n",
    "   WHERE REGEXP_LIKE(STATION,'^[P-R]')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "If you wanted to include all stations that have the letter\n",
    "P-R or p-e, you could add the condition within the brackets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STATION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>West Ruislip</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Ruislip Gardens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>South Ruislip</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Northolt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Greenford</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Snaresbrook</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>South Woodford</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Woodford</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Buckhurst Hill</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Epping</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>34 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            STATION\n",
       "0      West Ruislip\n",
       "1   Ruislip Gardens\n",
       "2     South Ruislip\n",
       "3          Northolt\n",
       "4         Greenford\n",
       "..              ...\n",
       "29      Snaresbrook\n",
       "30   South Woodford\n",
       "31         Woodford\n",
       "32   Buckhurst Hill\n",
       "33           Epping\n",
       "\n",
       "[34 rows x 1 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT STATION FROM CENTRAL_LINE\n",
    "   WHERE REGEXP_LIKE(STATION,'[p-rP-R]')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "Back to our SSN question. Can a regular expression pattern\n",
    "be used to determine whether or not the string is in the\n",
    "correct format? The data will be in the format XXX-XX-XXXX\n",
    "so the regular expression needs to find the three numeric\n",
    "values separated by dashes.\n",
    "\n",
    "The number pattern can be represented with the bracket\n",
    "expression [0-9]. To specify the number of characters that\n",
    "need to be found, we use the braces {} to specify the exact\n",
    "number required.\n",
    "\n",
    "For the three numbers in the pattern we can use [0-9]{3},\n",
    "[0-9]{2}, and [0-9]{4}. Adding in the dashes gives us the\n",
    "final pattern. The SQL below checks to see if a SSN is\n",
    "correct."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Valid</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       1\n",
       "0  Valid"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "VALUES\n",
    "  CASE \n",
    "    WHEN REGEXP_LIKE('123-34-1422','[0-9]{3}-[0-9]{2}-[0-9]{4}') THEN 'Valid'\n",
    "    ELSE 'Invalid'\n",
    "  END"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "The SSN is valid in the example above. Here are some other\n",
    "examples to show whether or not the regular expression picks\n",
    "up all of the errors."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SSN</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>123-34-1322</td>\n",
       "      <td>Valid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ABC-34-9999</td>\n",
       "      <td>Invalid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>X123-44-0001</td>\n",
       "      <td>Valid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>123X-Y44-Z0001</td>\n",
       "      <td>Invalid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>111-222-111</td>\n",
       "      <td>Invalid</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              SSN        2\n",
       "0     123-34-1322    Valid\n",
       "1     ABC-34-9999  Invalid\n",
       "2    X123-44-0001    Valid\n",
       "3  123X-Y44-Z0001  Invalid\n",
       "4     111-222-111  Invalid"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH SSNS(SSN) AS (\n",
    "   VALUES \n",
    "      '123-34-1322',\n",
    "      'ABC-34-9999',\n",
    "      'X123-44-0001',\n",
    "      '123X-Y44-Z0001',\n",
    "      '111-222-111'\n",
    "  )\n",
    "SELECT SSN,\n",
    "  CASE \n",
    "    WHEN REGEXP_LIKE(SSN,'[0-9]{3}-[0-9]{2}-[0-9]{4}') THEN 'Valid'\n",
    "    ELSE 'Invalid'\n",
    "  END\n",
    "FROM SSNS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "If you check closely, one of the strings was marked as\n",
    "valid, although it is not correct (X123-44-0001). The reason\n",
    "this occurred is that the pattern was found after the \"X\"\n",
    "and it was correct. To prevent this from happening, we need\n",
    "to anchor the pattern at the beginning to avoid this\n",
    "situation. A better pattern would be to anchor both ends of\n",
    "the pattern so there is no possibility of other characters\n",
    "being at the beginning or end of the pattern."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SSN</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>123-34-1322</td>\n",
       "      <td>Valid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ABC-34-9999</td>\n",
       "      <td>Invalid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>X123-44-0001</td>\n",
       "      <td>Invalid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>123X-Y44-Z0001</td>\n",
       "      <td>Invalid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>111-222-111</td>\n",
       "      <td>Invalid</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              SSN        2\n",
       "0     123-34-1322    Valid\n",
       "1     ABC-34-9999  Invalid\n",
       "2    X123-44-0001  Invalid\n",
       "3  123X-Y44-Z0001  Invalid\n",
       "4     111-222-111  Invalid"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH SSNS(SSN) AS (\n",
    "   VALUES \n",
    "      '123-34-1322',\n",
    "      'ABC-34-9999',\n",
    "      'X123-44-0001',\n",
    "      '123X-Y44-Z0001',\n",
    "      '111-222-111'\n",
    "  )\n",
    "SELECT SSN,\n",
    "  CASE \n",
    "    WHEN REGEXP_LIKE(SSN,'^[0-9]{3}-[0-9]{2}-[0-9]{4}$') THEN 'Valid'\n",
    "    ELSE 'Invalid'\n",
    "  END\n",
    "FROM SSNS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='special'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "## Special Patterns \n",
    " \n",
    "The previous example used the [0-9] syntax to request that\n",
    "only numbers be found in the pattern. There are some\n",
    "predefined patterns that define these common patterns. The\n",
    "first argument is Posix format (if it exists), the second is\n",
    "the escape character equivalent, and the final one is the\n",
    "raw pattern it represents.\n",
    " \n",
    "|Posix      | Escape   | Pattern         | Meaning\n",
    "|:----------| :--------| :---------------|:------------------------\n",
    "|[:alnum:]  |          | [A-Za-z0-9]     | Alphanumeric characters\n",
    "|\t\t    | \\w       | [A-Za-z0-9\\_]    | Alphanumeric characters plus \"\\_\"\n",
    "|\t\t    | \\W       | [^A-Za-z0-9\\_]   | Non-word characters\n",
    "|[:alpha:]  | \\a       | [A-Za-z]        | Alphabetic characters\n",
    "|[:blank:]  | \\s, \\t   |                 | Space and tab\n",
    "|\t\t    | \\b       |                 | Word boundaries\n",
    "|[:cntrl:]  |          | [\\x00-\\x1F\\x7F] | Control characters\n",
    "|[:digit:]  | \\d       | [0-9]           | Digits\n",
    "|\t\t    | \\D       | [^0-9]          | Non-digits\n",
    "|[:graph:]  |          | [\\x21-\\x7E]     | Visible characters\n",
    "|[:lower:]  | \\l       | [a-z]           | Lowercase letters\n",
    "|[:print:]  | \\p       | [\\x20-\\x7E]     | Visible characters and the space character\n",
    "|[:punct:]  |          | [][!\"#\\$%&'()\\*+,./:;<=>?@\\^\\_`{<code>&#124;</code>}~-] | Punctuation characters\n",
    "|[:space:]  | \\s       | [ \\t\\r\\n\\v\\f]   | Whitespace characters\n",
    "|\t\t    | \\S       | [^ \\t\\r\\n\\v\\f]  | Non-whitespace characters\n",
    "|[:upper:]  | \\u       | [A-Z]           | Uppercase letters\n",
    "|[:xdigit:] | \\x       | [A-Fa-f0-9]     | Hexadecimal digits\n",
    "\n",
    "For instance, the following three statements will produce\n",
    "the same result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>1</th>\n",
       "      <th>SSN</th>\n",
       "      <th>3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Original</td>\n",
       "      <td>123-34-1322</td>\n",
       "      <td>Valid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Original</td>\n",
       "      <td>ABC-34-9999</td>\n",
       "      <td>Invalid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Original</td>\n",
       "      <td>X123-44-0001</td>\n",
       "      <td>Invalid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Original</td>\n",
       "      <td>123X-Y44-Z0001</td>\n",
       "      <td>Invalid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Original</td>\n",
       "      <td>111-222-111</td>\n",
       "      <td>Invalid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Escape</td>\n",
       "      <td>123-34-1322</td>\n",
       "      <td>Valid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Escape</td>\n",
       "      <td>ABC-34-9999</td>\n",
       "      <td>Invalid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Escape</td>\n",
       "      <td>X123-44-0001</td>\n",
       "      <td>Invalid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Escape</td>\n",
       "      <td>123X-Y44-Z0001</td>\n",
       "      <td>Invalid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Escape</td>\n",
       "      <td>111-222-111</td>\n",
       "      <td>Invalid</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>15 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           1             SSN        3\n",
       "0   Original     123-34-1322    Valid\n",
       "1   Original     ABC-34-9999  Invalid\n",
       "2   Original    X123-44-0001  Invalid\n",
       "3   Original  123X-Y44-Z0001  Invalid\n",
       "4   Original     111-222-111  Invalid\n",
       "..       ...             ...      ...\n",
       "10    Escape     123-34-1322    Valid\n",
       "11    Escape     ABC-34-9999  Invalid\n",
       "12    Escape    X123-44-0001  Invalid\n",
       "13    Escape  123X-Y44-Z0001  Invalid\n",
       "14    Escape     111-222-111  Invalid\n",
       "\n",
       "[15 rows x 3 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH SSNS(SSN) AS (\n",
    "   VALUES \n",
    "      '123-34-1322',\n",
    "      'ABC-34-9999',\n",
    "      'X123-44-0001',\n",
    "      '123X-Y44-Z0001',\n",
    "      '111-222-111'\n",
    "  )\n",
    "SELECT 'Original', SSN,\n",
    "  CASE \n",
    "    WHEN REGEXP_LIKE(SSN,'^[0-9]{3}-[0-9]{2}-[0-9]{4}$') THEN 'Valid'\n",
    "    ELSE 'Invalid'\n",
    "  END\n",
    "FROM SSNS\n",
    "UNION ALL\n",
    "SELECT 'Posix', SSN,\n",
    "  CASE \n",
    "    WHEN REGEXP_LIKE(SSN,'^[:digit:]{3}-[:digit:]{2}-[:digit:]{4}$') THEN 'Valid'\n",
    "    ELSE 'Invalid'\n",
    "  END\n",
    "FROM SSNS\n",
    "UNION ALL\n",
    "SELECT 'Escape', SSN,\n",
    "  CASE \n",
    "    WHEN REGEXP_LIKE(SSN,'^\\d{3}-\\d{2}-\\d{4}$') THEN 'Valid'\n",
    "    ELSE 'Invalid'\n",
    "  END\n",
    "FROM SSNS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='negate'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "## Negating Patterns \n",
    " \n",
    "Up to this point in time, the patterns that have been used\n",
    "are looking for a positive match. In some cases you may want\n",
    "to find values that do not match. The easiest way is to\n",
    "negate the actual REGEXP_LIKE expression. The following\n",
    "expression finds all of the stations that start with \"West\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STATION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>West Ruislip</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>West Acton</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        STATION\n",
       "0  West Ruislip\n",
       "1    West Acton"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT STATION FROM CENTRAL_LINE\n",
    "  WHERE REGEXP_LIKE(STATION,'^West')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "Adding the NOT modifier in front of the REGEXP function\n",
    "gives us the stations that do not begin with West."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STATION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Ruislip Gardens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>South Ruislip</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Northolt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Greenford</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Perivale</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>Buckhurst Hill</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Loughton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>Debden</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Theydon Bois</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>Epping</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>47 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            STATION\n",
       "0   Ruislip Gardens\n",
       "1     South Ruislip\n",
       "2          Northolt\n",
       "3         Greenford\n",
       "4          Perivale\n",
       "..              ...\n",
       "42   Buckhurst Hill\n",
       "43         Loughton\n",
       "44           Debden\n",
       "45     Theydon Bois\n",
       "46           Epping\n",
       "\n",
       "[47 rows x 1 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT STATION FROM CENTRAL_LINE\n",
    "  WHERE NOT REGEXP_LIKE(STATION,'^West') "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "You can also negate some of the searches in a pattern by\n",
    "using the [^...] syntax where the ^ tells the regular\n",
    "expression not to match the following characters. The\n",
    "expression [^0-9] would mean match any characters which are\n",
    "not numeric.\n",
    "\n",
    "However, regular expressions have something called negative\n",
    "lookarounds which basically mean find the pattern which does\n",
    "not match. You create this pattern by adding the (?!..) at\n",
    "the beginning of the string. The same query (finding\n",
    "stations that don't start with West) would be written with\n",
    "this lookaround logic found in the SQL below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STATION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Ruislip Gardens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>South Ruislip</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Northolt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Greenford</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Perivale</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>Buckhurst Hill</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Loughton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>Debden</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Theydon Bois</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>Epping</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>47 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            STATION\n",
       "0   Ruislip Gardens\n",
       "1     South Ruislip\n",
       "2          Northolt\n",
       "3         Greenford\n",
       "4          Perivale\n",
       "..              ...\n",
       "42   Buckhurst Hill\n",
       "43         Loughton\n",
       "44           Debden\n",
       "45     Theydon Bois\n",
       "46           Epping\n",
       "\n",
       "[47 rows x 1 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT STATION FROM CENTRAL_LINE\n",
    "  WHERE REGEXP_LIKE(STATION,'^(?!West)')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='capture'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "## Capturing Parenthesis \n",
    " \n",
    "The previous example used something called a negative\n",
    "lookaround with capturing parenthesis. When you place a\n",
    "pattern within a set of brackets (...) the string that\n",
    "matches this pattern is \"remembered\". The strings that are\n",
    "matched can be used in subsequent parts of your regular\n",
    "expression. This allows a form of programming within your\n",
    "regular expression!\n",
    "\n",
    "Each set of parentheses that are matched are associated with\n",
    "a number, starting at one and incrementing for each\n",
    "subsequent pattern match. For instance, the following\n",
    "pattern will have three matches:\n",
    "<pre>\n",
    "^([0-9]{3})-([0-9]{3})-([0-9]{3})$\n",
    "</pre>\n",
    "This is similar to the SSN example used earlier on in this\n",
    "section. The difference in this example is that each block\n",
    "of numbers is exactly the same (3 digits). This pattern will\n",
    "match any sequence of numbers in the format 123-456-789."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SSN</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>123-456-789</td>\n",
       "      <td>Valid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>123-555-123</td>\n",
       "      <td>Valid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>890-533-098</td>\n",
       "      <td>Valid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>123-456-456</td>\n",
       "      <td>Valid</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           SSN      2\n",
       "0  123-456-789  Valid\n",
       "1  123-555-123  Valid\n",
       "2  890-533-098  Valid\n",
       "3  123-456-456  Valid"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH SSNS(SSN) AS (\n",
    "   VALUES \n",
    "      '123-456-789',\n",
    "      '123-555-123',\n",
    "      '890-533-098',\n",
    "      '123-456-456'\n",
    "  )\n",
    "SELECT SSN,\n",
    "  CASE \n",
    "    WHEN REGEXP_LIKE(SSN,'^([0-9]{3})-([0-9]{3})-([0-9]{3})$') THEN 'Valid'\n",
    "    ELSE 'Invalid'\n",
    "  END\n",
    "FROM SSNS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "All of these numbers fit the pattern and should be valid.\n",
    "When one of the capturing parenthesis matches, it will\n",
    "remember the string that it matched. For instance, in the\n",
    "first example (123-456-789), the first match will find the\n",
    "string '123'. The second match will find '456' and so on. We\n",
    "can refer to these matched strings with the special control\n",
    "characters \\n where n represents the capturing parenthesis\n",
    "location. So \\1 would refer to the '123' that was found. \\2\n",
    "would be for '456'.\n",
    "\n",
    "The regular expression will be updated so that the last part\n",
    "of the pattern needs to be numeric (0-9) but can't be same\n",
    "as the first match."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SSN</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>123-456-789</td>\n",
       "      <td>Valid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>123-555-123</td>\n",
       "      <td>Invalid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>890-533-098</td>\n",
       "      <td>Valid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>123-456-456</td>\n",
       "      <td>Valid</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           SSN        2\n",
       "0  123-456-789    Valid\n",
       "1  123-555-123  Invalid\n",
       "2  890-533-098    Valid\n",
       "3  123-456-456    Valid"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH SSNS(SSN) AS (\n",
    "   VALUES \n",
    "      '123-456-789',\n",
    "      '123-555-123',\n",
    "      '890-533-098',\n",
    "      '123-456-456'\n",
    "  )\n",
    "SELECT SSN,\n",
    "  CASE \n",
    "    WHEN REGEXP_LIKE(SSN,'^([0-9]{3})-([0-9]{3})-(?!\\1)([0-9]{3})$') THEN 'Valid'\n",
    "    ELSE 'Invalid'\n",
    "  END\n",
    "FROM SSNS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "In many cases it may be easier to find the patterns that\n",
    "match and then negate the REGEXP statement! The (?...)\n",
    "syntax is used for a variety of purposes in regular\n",
    "expressions:\n",
    "\n",
    "| Pattern   | Result\n",
    "|:----------| :-----------\n",
    "| (?: ... ) | Non-capturing parentheses. Groups the included pattern, but does not provide capturing of matching text. More efficient than capturing parentheses.\n",
    "| (?> ... ) | Atomic-match parentheses. First match of the parenthesized subexpression is the only one tried. If it does not lead to an overall pattern match, back up the search for a match to a position before the \"(?>\"\n",
    "| (?# ... ) | Free-format comment (?# comment )\n",
    "| (?= ... ) | Look-ahead assertion. True if the parenthesized pattern matches at the current input position, but does not advance the input position.\n",
    "| (?! ... ) | Negative look-ahead assertion. True if the parenthesized pattern does not match at the current input position. Does not advance the input position.\n",
    "| (?<= ... )| Look-behind assertion. True if the parenthesized pattern matches text that precedes the current input position. The last character of the match is the input character just before the current position. Does not alter the input position. The length of possible strings that is matched by the look-behind pattern must not be unbounded (no * or + operators.)\n",
    "| (?<!... ) | Negative Look-behind assertion. True if the parenthesized pattern does not match text that precedes preceding the current input position. The last character of the match is the input character just before the current position. Does not alter the input position. The length of possible strings that is matched by the look-behind pattern must not be unbounded (no * or + operators.)\n",
    "\n",
    "\n",
    "For efficiency in matching, the best approach is to place\n",
    "strings that you are searching for in non-capturing\n",
    "parentheses (?:...) rather than the generic () parenthesis.\n",
    "The following example finds all stations with \"West\" in the\n",
    "name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STATION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>West Ruislip</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>West Acton</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        STATION\n",
       "0  West Ruislip\n",
       "1    West Acton"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT STATION FROM CENTRAL_LINE\n",
    "  WHERE REGEXP_LIKE(STATION,'(West)')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "The following SQL is equivalent, except that the matched\n",
    "pattern is not kept for future use in matching."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STATION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>West Ruislip</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>West Acton</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        STATION\n",
       "0  West Ruislip\n",
       "1    West Acton"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT STATION FROM CENTRAL_LINE\n",
    "  WHERE REGEXP_LIKE(STATION,'(?:West)')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='performance'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "## Performance Considerations of Regular Expressions \n",
    " \n",
    "We know that there are better ways to write Regular\n",
    "Expressions (like the use of non-capturing parenthesis. How\n",
    "much of a difference does this make? In order to find out,\n",
    "the following SQL will take the existing table and insert\n",
    "into a temporary table 1000 times (for close to 50000\n",
    "records). \n",
    "\n",
    "This SQL will generate the new table and fill it with the\n",
    "base CENTRAL_LINE data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>49049</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       1\n",
       "0  49049"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%sql -d -q\n",
    "DROP TABLE TEMP_LINE\n",
    "@\n",
    "\n",
    "CREATE TABLE TEMP_LINE AS (SELECT * FROM CENTRAL_LINE) DEFINITION ONLY NOT LOGGED INITIALLY\n",
    "@\n",
    " \n",
    "BEGIN\n",
    "   DECLARE I INTEGER DEFAULT 0;   \n",
    "   WHILE I <= 1000 DO\n",
    "      INSERT INTO TEMP_LINE SELECT * FROM CENTRAL_LINE;\n",
    "      SET I = I + 1;\n",
    "   END WHILE;\n",
    "END\n",
    "@\n",
    "\n",
    "SELECT COUNT(*) FROM TEMP_LINE\n",
    "@"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "We will run four queries in the following SQL.\n",
    "\n",
    " - Search for West using the LIKE statement \n",
    " - Search for West using a regular expression with no matching () \n",
    " - Search for West using a regular expression with matching ()\n",
    " - Search for West using a regular expression with non-capturing parenthesis (?:)\n",
    "\n",
    "The queries are run as many times as possible per second to get consistent results. Odds are that there will be variations in the results on your system!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {
    "hideCode": false,
    "hideOutput": true,
    "hidePrompt": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total iterations in 1 second(s): 168\n",
      "Total iterations in 1 second(s): 27\n",
      "Total iterations in 1 second(s): 26\n",
      "Total iterations in 1 second(s): 27\n"
     ]
    }
   ],
   "source": [
    "results_like         = %sql -t SELECT COUNT(*) FROM TEMP_LINE WHERE STATION LIKE '%West%'\n",
    "results_string       = %sql -t SELECT COUNT(*) FROM TEMP_LINE WHERE REGEXP_LIKE(STATION,'West')\n",
    "results_capturing    = %sql -t SELECT COUNT(*) FROM TEMP_LINE WHERE REGEXP_LIKE(STATION,'(West)')\n",
    "results_noncapturing = %sql -t SELECT COUNT(*) FROM TEMP_LINE WHERE REGEXP_LIKE(STATION,'(?:West)')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The results are placed into a temporary table for easier formatting."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Command completed.\n",
      "Command completed.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TYPE</th>\n",
       "      <th>RESULT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>LIKE</td>\n",
       "      <td>168.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>REGX STRING</td>\n",
       "      <td>27.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>REGX CAPTURE</td>\n",
       "      <td>26.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>REGX NONCAPTURE</td>\n",
       "      <td>27.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              TYPE  RESULT\n",
       "0             LIKE   168.0\n",
       "1      REGX STRING    27.0\n",
       "2     REGX CAPTURE    26.0\n",
       "3  REGX NONCAPTURE    27.0"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql -q DROP TABLE RESULTS\n",
    "%sql CREATE TABLE RESULTS(TYPE VARCHAR(16), RESULT DEC(9,2))\n",
    "%sql INSERT INTO RESULTS VALUES ('LIKE', {results_like} ), \\\n",
    "  ('REGX STRING', {results_string} ), \\\n",
    "  ('REGX CAPTURE', {results_capturing} ), \\\n",
    "  ('REGX NONCAPTURE', {results_noncapturing} )\n",
    "%sql SELECT * FROM RESULTS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The results are clearer when we plot them all on one graph!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.figure.Figure at 0x7f589c1f26d8>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAeYAAAG1CAYAAAAsiVeWAAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAALEgAACxIB0t1+/AAAIABJREFUeJzt3XtYVXWixvF3I6ImchUlvI6p5Q19Oni8lINlNz2mTBc7\nOjhq3rM6ectGGp95TvnYDSmnOPaMl8yZeZzmGGSWWhOJCefpQdEyTR118oYCsglFpQ3sff4Q90gq\nIMJev7339/MXe63tXi/7V7x7rbXXb9lcLpdLAADACAFWBwAAAP9CMQMAYBCKGQAAg1DMAAAYhGIG\nAMAgFDMAAAahmAEAMEig1QEuy8vLszpCo4mJifHp38/XMX7ei7Hzbr4+fjExMddczh4zAAAGoZgB\nADAIxQwAgEEoZgAADEIxAwBgEIoZAACDUMwAABjEmOuYAQC4UcOGDdMvfvELVVZW6tZbb9XChQsV\nHBys06dPa8KECerQoYP7uY8//rgefPBBffrpp/rf//1f2Ww2OZ1OTZ48WXfffbeee+45zZw5U7ff\nfrsk6fTp0/rtb3+r1atXa/fu3frrX/+qJUuWuF8vKSlJ+fn5unjxokpKShQdHS1JmjNnjnr27Fnv\n34liBgA0iMqpoxr2BT/ZUetTgoKCtGLFCknSkiVLlJ6ersTEREmXJvC4vO6ywsJC/fnPf9a7776r\n4OBgXbx4UT/++GO94i1evFiStHPnTqWlpenll1+u1+v8HMUMAPAJvXr10uHDh2t8TnFxsVq0aKEW\nLVpIUrWfTUExAwC8XmVlpXJzczVixAj3sry8PE2ZMsX9+Nlnn1WvXr0UHh6ucePG6c4779SQIUM0\nePBgKyJfF8UMAPBaDodDU6ZM0ZkzZ9SpUyf927/9m3vdtQ5lS9Jrr72m/fv3Kzc3V++8844OHjyo\niRMnymazXfXcay1rbH5ZzA1+HqQWxz26NanJHzd4eIsAYI3L55jLysr0/PPPKz09XY8++miN/8Zm\ns6lHjx7q0aOH4uLi9Oqrr2rixIkKCQnRuXPn3M87e/asQkNDG/tXuAqXSwEAvF7z5s31zDPP6G9/\n+5sqKyuv+7wzZ87o4MGD7seHDh1S27ZtJUn9+vXT559/LpfLJUnasmWL+vXr17jBr8Ev95gBAL6n\nW7du6tKli7744gvFxsZedY55+PDhuuuuu7R8+XKdOXNGQUFBCgsL0+zZsyVJI0eO1LFjx9z/5vbb\nb9fUqVPd/z43N1ePP/64+/Hvf/979erVq8F/D5vr8kcDi3nynpuePpTtaRzKbli+fk9YX8bYeTdf\nHz/uxwwAgBegmAEAMAjFDACAQShmAAAMQjEDAGAQihkAAIPUeh1zamqqcnNzFRoaquTkZPfyTZs2\nacuWLQoICNCdd97pvptHWlqaMjIyFBAQoEmTJllycTYAAN6q1mIeOnSoHnroIb3zzjvuZd999512\n7Nih119/XU2bNlVJSYkk6cSJE8rOztbSpUtVXFysl156SW+99ZYCAtgxBwCgLmptzJ49eyo4OLja\nss8++0yjR49W06ZNJck9l2hOTo4GDx6spk2bqk2bNoqOjtahQ4caITYAAL6pXlNynjp1Svv379e6\ndevUtGlTjR8/Xl27dpXdble3bt3cz4uIiJDdbm+wsAAA+Lp6FbPT6VRpaakWL16sw4cPKyUlRW+/\n/fZNBbne1GSNwdN3e/I0T76X/oL31Hsxdt7NH8evXsUcERGhf//3f5fNZlPXrl0VEBCgc+fOKSIi\nQkVFRe7n2e12RURE1Ok1fXk+VE/jvWxYvj5fry9j7Lybr49fg86V3b9/f+3du1fSpRKoqKhQq1at\nFBcXp+zsbJWXl6ugoECnTp1S165d658aAAA/U+se85tvvql9+/bp3LlzmjFjhsaMGaN7771Xqamp\nmjt3rgIDAzVr1izZbDZ16NBBgwYN0pw5cxQQEKDJkyfzjWwAAG4At330Qdz2sWH5+uE0X8bYeTdf\nHz9u+wgAgBegmAEAMAjFDACAQShmAAAMQjEDAGAQihkAAINQzAAAGIRiBgDAIBQzAAAGoZgBADAI\nxQwAgEEoZgAADEIxAwBgEIoZAACDUMwAABiEYgYAwCAUMwAABqGYAQAwCMUMAIBBKGYAAAxCMQMA\nYBCKGQAAg1DMAAAYhGIGAMAgFDMAAAaptZhTU1M1ZcoUzZ0796p1H3/8scaMGaOzZ8+6l6WlpemZ\nZ57Rf/3Xf2n37t0NmxYAAB9XazEPHTpUCxcuvGr5mTNn9O2336p169buZSdOnFB2draWLl2qpKQk\nrVy5Uk6ns2ETAwDgw2ot5p49eyo4OPiq5WvWrNGvf/1r2Ww297KcnBwNHjxYTZs2VZs2bRQdHa1D\nhw41bGIAAHxYvc4x5+TkKCIiQp07d6623G63KzIy0v04IiJCdrv9pgICAOBPAm/0H/z0009KS0vT\niy++2KBBYmJiGvT1anLcY1uyhiffS3/Be+q9GDvv5o/jd8PFnJ+fr4KCAs2fP1+SVFRUpAULFmjJ\nkiWKiIhQUVGR+7l2u10RERF1et28vLwbjYLr4L1sWDExMbynXoqx826+Pn7X+9Bxw8XcsWNHrVix\nwv141qxZWrJkiUJCQhQXF6dly5Zp5MiRKi4u1qlTp9S1a9f6pwYAwM/UWsxvvvmm9u3bp3PnzmnG\njBkaM2aM7r333ms+t0OHDho0aJDmzJmjgIAATZ48WQEBXCoNAEBd2Vwul8vqEJJnD79WTh3lsW1Z\nockfN1gdwaf4+uE0X8bYeTdfH7/rHcpmdxYAAINQzAAAGIRiBgDAIBQzAAAGoZgBADAIxQwAgEEo\nZgAADEIxAwBgEIoZAACDUMwAABiEYgYAwCAUMwAABqGYAQAwCMUMAIBBKGYAAAxCMQMAYBCKGQAA\ng1DMAAAYhGIGAMAgFDMAAAahmAEAMAjFDACAQShmAAAMQjEDAGAQihkAAIME1vaE1NRU5ebmKjQ0\nVMnJyZKktWvXaufOnQoMDFTbtm311FNPqWXLlpKktLQ0ZWRkKCAgQJMmTVK/fv0a9zcAAMCH1LrH\nPHToUC1cuLDastjYWCUnJ+uNN97QrbfeqrS0NEnSiRMnlJ2draVLlyopKUkrV66U0+lsnOQAAPig\nWou5Z8+eCg4Orrasb9++atKkiSSpe/fustvtkqScnBwNHjxYTZs2VZs2bRQdHa1Dhw41QmwAAHzT\nTZ9jzsjIcB+uttvtioyMdK+LiIhwlzYAAKhdreeYa/Lhhx+qSZMmGjJkyE0HiYmJuenXqKvjHtuS\nNTz5XvoL3lPvxdh5N38cv3oX89atW7Vz504tWrRINptN0qU95KKiIvdz7Ha7IiIi6vR6eXl59Y2C\nn+G9bFgxMTG8p16KsfNuvj5+1/vQUa9D2bt379ZHH32kBQsWqFmzZu7lcXFxys7OVnl5uQoKCnTq\n1Cl17dq1fokBAPBDte4xv/nmm9q3b5/OnTunGTNmaMyYMUpLS1NFRYVeeuklSVK3bt00bdo0dejQ\nQYMGDdKcOXMUEBCgyZMnKyCAS6UBAKgrm8vlclkdQvLs4dfKqaM8ti0rNPnjBqsj+BRfP5zmyxg7\n7+br49egh7IBAEDjoJgBADAIxQwAgEEoZgAADEIxAwBgEIoZAACDUMwAABiEYgYAwCAUMwAABqGY\nAQAwCMUMAIBBKGYAAAxCMQMAYBCKGQAAg1DMAAAYhGIGAMAgFDMAAAahmAEAMAjFDACAQShmAAAM\nQjEDAGAQihkAAINQzAAAGIRiBgDAIBQzAAAGCaztCampqcrNzVVoaKiSk5MlSaWlpUpJSVFhYaGi\noqI0e/ZsBQcHS5LS0tKUkZGhgIAATZo0Sf369Wvc3wAAAB9S6x7z0KFDtXDhwmrL0tPT1adPHy1b\ntkx9+vRRenq6JOnEiRPKzs7W0qVLlZSUpJUrV8rpdDZOcgAAfFCtxdyzZ0/33vBlOTk5io+PlyTF\nx8crJyfHvXzw4MFq2rSp2rRpo+joaB06dKgRYgMA4JtqPZR9LSUlJQoPD5ckhYWFqaSkRJJkt9vV\nrVs39/MiIiJkt9vr9JoxMTH1iVIvxz22JWt48r30F7yn3oux827+OH71KuYr2Ww22Wy2mw6Sl5d3\n06+BS3gvG1ZMTAzvqZdi7Lybr4/f9T501Otb2aGhoSouLpYkFRcXKyQkRNKlPeSioiL38+x2uyIi\nIuqzCQAA/FK9ijkuLk6ZmZmSpMzMTPXv39+9PDs7W+Xl5SooKNCpU6fUtWvXhksLAICPq/VQ9ptv\nvql9+/bp3LlzmjFjhsaMGaOEhASlpKQoIyPDfbmUJHXo0EGDBg3SnDlzFBAQoMmTJysggEulAQCo\nK5vL5XJZHULy7HnRyqmjPLYtKzT54warI/gUXz/P5csYO+/m6+PXoOeYAQBA46CYAQAwCMUMAIBB\nKGYAAAxCMQMAYBCKGQAAg1DMAAAYhGIGAMAgFDMAAAahmAEAMAjFDACAQShmAAAMQjEDAGAQihkA\nAINQzAAAGIRiBgDAIBQzAAAGoZgBADAIxQwAgEEoZgAADEIxAwBgEIoZAACDUMwAABiEYgYAwCAU\nMwAABgm8mX+8ceNGZWRkyGazqUOHDnrqqafkcDiUkpKiwsJCRUVFafbs2QoODm6ovAAA+LR67zHb\n7XZt2rRJr7zyipKTk+V0OpWdna309HT16dNHy5YtU58+fZSent6QeQEA8Gk3dSjb6XTK4XCosrJS\nDodD4eHhysnJUXx8vCQpPj5eOTk5DRIUAAB/UO9D2REREXr44Yc1c+ZMBQUFqW/fvurbt69KSkoU\nHh4uSQoLC1NJSUmdXi8mJqa+UW7YcY9tyRqefC/9Be+p92LsvJs/jl+9i7m0tFQ5OTl65513dMst\nt2jp0qXatm1btefYbDbZbLY6vV5eXl59o+BneC8bVkxMDO+pl2LsvJuvj9/1PnTU+1D2nj171KZN\nG4WEhCgwMFADBgzQwYMHFRoaquLiYklScXGxQkJC6rsJAAD8Tr2LuXXr1vrHP/6hn376SS6XS3v2\n7FG7du0UFxenzMxMSVJmZqb69+/fYGEBAPB19T6U3a1bNw0cOFALFixQkyZN1LlzZ913330qKytT\nSkqKMjIy3JdLAQCAurmp65jHjBmjMWPGVFvWtGlTLVq06KZCAQDgr5j5CwAAg1DMAAAYhGIGAMAg\nFDMAAAahmAEAMAjFDACAQShmAAAMQjEDAGAQihkAAINQzAAAGIRiBgDAIBQzAAAGoZgBADAIxQwA\ngEEoZgAADEIxAwBgEIoZAACDUMwAABiEYgYAwCAUMwAABqGYAQAwCMUMAIBBKGYAAAxCMQMAYJDA\nm/nH58+f1/Lly3X8+HHZbDbNnDlTMTExSklJUWFhoaKiojR79mwFBwc3VF4AAHzaTRXz6tWr1a9f\nP82dO1cVFRX66aeflJaWpj59+ighIUHp6elKT09XYmJiQ+UFAMCn1ftQ9oULF/T999/r3nvvlSQF\nBgaqZcuWysnJUXx8vCQpPj5eOTk5DZMUAAA/UO895oKCAoWEhCg1NVVHjx5Vly5dNHHiRJWUlCg8\nPFySFBYWppKSkgYLCwCAr6t3MVdWVuqf//ynnnzySXXr1k2rV69Wenp6tefYbDbZbLY6vV5MTEx9\no9yw4x7bkjU8+V76C95T78XYeTd/HL96F3NkZKQiIyPVrVs3SdLAgQOVnp6u0NBQFRcXKzw8XMXF\nxQoJCanT6+Xl5dU3Cn6G97JhxcTE8J56KcbOu/n6+F3vQ0e9zzGHhYUpMjLS/abt2bNH7du3V1xc\nnDIzMyVJmZmZ6t+/f303AQCA37mpb2U/+eSTWrZsmSoqKtSmTRs99dRTcrlcSklJUUZGhvtyKQAA\nUDc3VcydO3fWK6+8ctXyRYsW3czLAgDgt5j5CwAAg1DMAAAYhGIGAMAgFDMAAAahmAEAMAjFDACA\nQShmAAAMQjEDAGAQihkAAINQzAAAGIRiBgDAIBQzAAAGoZgBADAIxQwAgEEoZgAADEIxAwBgEIoZ\nAACDUMwAABiEYgYAwCAUMwAABqGYAQAwCMUMAIBBKGYAAAxCMQMAYBCKGQAAgwTe7As4nU698MIL\nioiI0AsvvKDS0lKlpKSosLBQUVFRmj17toKDgxsiKwAAPu+m95g//fRTtWvXzv04PT1dffr00bJl\ny9SnTx+lp6ff7CYAAPAbN1XMRUVFys3N1bBhw9zLcnJyFB8fL0mKj49XTk7OzSUEAMCP3FQxv/fe\ne0pMTJTNZnMvKykpUXh4uCQpLCxMJSUlN5cQAAA/Uu9zzDt37lRoaKi6dOmivXv3XvM5NputWmnX\nJCYmpr5Rbthxj23JGp58L/0F76n3Yuy8mz+OX72L+cCBA9qxY4d27dolh8OhixcvatmyZQoNDVVx\ncbHCw8NVXFyskJCQOr1eXl5efaPgZ3gvG1ZMTAzvqZdi7Lybr4/f9T501LuYx40bp3HjxkmS9u7d\nq48//ljPPvus1q5dq8zMTCUkJCgzM1P9+/ev7yYAAPA7DX4dc0JCgr799ls9++yz2rNnjxISEhp6\nEwAA+Kybvo5Zknr16qVevXpJklq1aqVFixY1xMsCAOB3mPkLAACDUMwAABiEYgYAwCAUMwAABqGY\nAQAwCMUMAIBBKGYAAAxCMQMAYBCKGQAAg1DMAAAYhGIGAMAgFDMAAAahmAEAMAjFDACAQShmAAAM\nQjEDAGAQihkAAIMEWh0AuFGVU0d5dHvHPbo1qckfN3h4i0Dd8P+eZ7DHDACAQdhjBuAx7HEBtWOP\nGQAAg1DMAAAYhGIGAMAgFDMAAAahmAEAMEi9v5V95swZvfPOO/rxxx9ls9l03333acSIESotLVVK\nSooKCwsVFRWl2bNnKzg4uCEzAwDgs+pdzE2aNNH48ePVpUsXXbx4US+88IJiY2O1detW9enTRwkJ\nCUpPT1d6eroSExMbMjMAAD6r3oeyw8PD1aVLF0lSixYt1K5dO9ntduXk5Cg+Pl6SFB8fr5ycnIZJ\nCgCAH2iQc8wFBQX65z//qa5du6qkpETh4eGSpLCwMJWUlDTEJgAA8As3PfNXWVmZkpOTNXHiRN1y\nyy3V1tlsNtlstjq9TkxMzM1GqTNPzwbkaZ58L63A+Hkvxs67MX6ecVPFXFFRoeTkZA0ZMkQDBgyQ\nJIWGhqq4uFjh4eEqLi5WSEhInV4rLy/vZqLgCryX3o3x816MnXfz9Phd74NAvQ9lu1wuLV++XO3a\ntdPIkSPdy+Pi4pSZmSlJyszMVP/+/eu7CQAA/E6995gPHDigbdu2qWPHjpo/f74kaezYsUpISFBK\nSooyMjLcl0sBAIC6qXcx33HHHfrggw+uuW7RokX1DgQAgD9j5i8AAAxCMQMAYBCKGQAAg1DMAAAY\nhGIGAMAgFDMAAAahmAEAMAjFDACAQShmAAAMQjEDAGAQihkAAINQzAAAGIRiBgDAIBQzAAAGoZgB\nADAIxQwAgEEoZgAADEIxAwBgEIoZAACDUMwAABiEYgYAwCAUMwAABqGYAQAwCMUMAIBBKGYAAAwS\n2FgvvHv3bq1evVpOp1PDhg1TQkJCY20KAACf0Sh7zE6nUytXrtTChQuVkpKirKwsnThxojE2BQCA\nT2mUYj506JCio6PVtm1bBQYGavDgwcrJyWmMTQEA4FMapZjtdrsiIyPdjyMjI2W32xtjUwAA+JRG\nO8d8o2JiYjy3sU92eG5baHiMn/di7Lwb4+cRjbLHHBERoaKiIvfjoqIiRURENMamAADwKY1SzLfd\ndptOnTqlgoICVVRUKDs7W3FxcY2xKQAAfIrN5XK5GuOFc3NztWbNGjmdTt1zzz165JFHGmMzAAD4\nlEYrZgAAcOOY+QsAAINQzAAAGIRiBgDAIBQzUMXpdKqsrMz9+ODBg9q3b5/27dunixcvWpgMtdm2\nbZv75/3791dbt3nzZk/HwQ06efKk++fy8vJq6w4ePOjpOJajmBsYfyC815/+9Cdt2bLF/fitt97S\nhg0btH79eq1fv97CZKjNJ5984v559erV1dZ9+eWXno6DG7Rs2TL3zy+++GK1dStXrvR0HMtRzA2M\nPxDe67vvvtPIkSPdj1u2bKkXXnhBL774og4cOGBhMtTmyotLfn6hCReemI/xq45ibmD8B+a9XC6X\nmjRp4n7861//WpJks9mqHeKGeWw22zV/vtZjmIfxq86YubJ9Bf+Bea+KigpdvHhRLVq0kCT17dtX\nknThwoWrznvBLCdPntS8efPkcrmUn5+vefPmSbr0YaugoMDidKhNUVGRVq1addXPkvzyBkhMMNLA\nEhMTFR0d7f4DER0dLelffyDWrl1rcUJcz8aNG7Vnzx5NnTpVrVu3liQVFhZqxYoV6tWrl0aNGmVx\nQlxPYWFhjeujoqI8lAT1sXXr1hrXDx061CM5TEExN7Ca/kC4XC61adPGg2lwoz777DOlpaXpp59+\nksvlUosWLZSQkKAHHnjA6mioB6fTqaysLA0ZMsTqKKinM2fOuD8o+wuKuYFt3br1mp/uKisr9Yc/\n/EHPPfec50Phhl2+POryYW2Y7cKFC9qyZYvsdrvi4uIUGxurzZs3a+PGjerUqZOef/55qyOiFgcP\nHpTdblePHj0UGhqqo0ePKj09Xfv379f//M//WB3PozjH3MA2bdqkiooK3Xfffe5lZWVleuONNxQZ\nGWlhMtQmMzOzxvXx8fEeSoIb9fbbb6tly5bq3r27vvjiC6Wlpcnlcmn+/Pnq3Lmz1fFQi7Vr1yo3\nN1edOnXSRx99pL59++qLL77Qr371K82cOdPqeB5HMTew3/3ud1q8eLEcDodGjBihs2fPasmSJerd\nu7f7W74w0+HDh6+5fMeOHbLb7RSzwfLz85WcnCxJGjZsmKZNm6bU1FQFBQVZnAx1kZubq1dffVVB\nQUEqLS3VzJkzlZyc7Len/ijmBhYcHKzf/e53WrJkiYqLi7Vjxw7df//9GjFihNXRUIsnn3zS/bPL\n5dJXX32ljz76SN26deO2pYYLDPzXn7KAgABFRkZSyl4kKCjIPV7BwcG69dZb/baUJc4xN7ivv/5a\n0qVzlO+//7569+6tu+66y71+wIABVkVDHVRWVmrr1q36+OOP1a1bN/3qV79STEyM1bFQiyeeeELN\nmzeXdOlDlcPhULNmzeRyuWSz2bRmzRqLE6ImEydOVI8ePdyPv//++2qPFyxYYEUsy1DMDSw1NbXG\n9U899ZSHkuBGbd68WZs2bVLv3r01evRov/7EDnjSvn37alzfs2dPDyUxA8UMVHniiScUEhKikJCQ\napPBXN7reuONNyxMh5qUlpZetaxly5ZM6gOvRDE3sI0bN9a4/sq5mGEWJqnwXrNmzZLNZqs27W1Z\nWZk6d+6s6dOnc/TDcHPnzr1q1sRWrVqpV69eevjhh/3u+wIUcwP729/+dt11NptNjz32mAfTAP7t\n66+/1t///nclJSVZHQU1uNaH4tLSUmVmZqqsrEwzZsywIJV1+FZ2A3v88cevu+7KO0/BPL/5zW+u\neeiTLxB5rwEDBujDDz+0OgZqca2jUVFRUfrFL37hl5PDUMwetHHjRv3Hf/yH1TFwHe+//77VEdDA\nysrK5HQ6rY6Bm+CPB3UpZqAW58+f15YtW7iW2WDX+m5HaWmpdu7cqQcffNCCRLgRR44cuWrZ+fPn\n9dVXX1W7bMpfUMxAlTNnzmj9+vUqLi5W//79ddddd+mDDz5QZmam7r77bqvjoQaX5za/zGazKSws\nTM8884w6duxoUSrU1bXuuteqVSv17Nmz2vTG/oJibmA1nad0OBwWJEJdvfPOO+rRo4cGDBig3bt3\n67e//a06deqk5ORkhYWFWR0PNSgvL9e4ceOsjoF6evDBBzVw4ECrYxiDYm5gnKf0XqWlpRozZowk\nqV+/fpoxY4aeffZZBQQEWJwMtfnmm28oZi+WlpZGMV+BYgaucOVEFa1atdKFCxfcj4ODg62IhDpw\nOp3XnGTkMsYO3oTrmIEq15qk4jKbzaa3337bglSoi3HjxikiIoKx81KJiYmKjo6+arm/zrrHHjNQ\n5fe//z2ze3mp9u3b67XXXrM6BuqpTZs2fnejippQzECVN954Q6+++qrVMQC/ExgYyIfiK/CtFqAK\nZ3W814gRI3T27FkdPnxY58+ftzoObtDtt99udQSjsMcMVLHb7Vq1atV11z/55JMeTIMbUVlZqTlz\n5qht27YqKCjQ9OnTFRcXZ3Us1NGQIUM0f/58nT59Wh07dtTMmTPVvn17q2NZhmIGqgQFBalLly5W\nx0A9fPrpp1q6dKlCQkKUn5+vZcuWUcxeZNWqVRo/frx69OihHTt2aM2aNX594xGKGajSqlUrDR06\n1OoYqIfAwECFhIRIktq2bauKigqLE+FGOJ1OxcbGSpIGDRqk9PR0ixNZi2IGqgQG8r+DtyoqKqp2\nGuLnjzkNYbYLFy7o66+/dj8+f/58tccDBgywIpZluI4ZqFJQUKDg4GDdcsstkqTvvvtOOTk5ioqK\n0kMPPURxG2zr1q01rudIiNlSU1NrXP/UU095KIkZKGagysKFCzVv3jxFRETohx9+0EsvvaSEhAQd\nPXpUgYGBfnezdl/gcDi0c+dODRo0yOooqKcff/zR7+aqZxcAqOJwOBQRESFJ2rZtm+655x49/PDD\ncjqdfnmzdm/ldDq1e/duZWVl6dtvv9Udd9xBMXuZy4eyt2/frpMnT+rdd9+1OpJHUcxAlSsPHu3d\nu1djx46VJG5i4SX27dun7du3a9euXbrtttt04MABvf3222rWrJnV0VAHDodDOTk52r59u3744Qdd\nvHhR8+fP537MgD/r3bu3li5dqvDwcJWWlqp3796SpOLiYs4vG27GjBlq3bq1HnjgAY0fP14tWrTQ\nrFmzKGUv8dZbb2n//v2KjY3V8OHD1bt3bz3zzDPq1auX1dEswa4AUGXixIkaMGCAoqKi9NJLL7nL\n+Mcff9R//ud/WpwONRk4cKCKi4uVnZ2tnTt3qqys7Jr3RYeZTpw4oZYtW6pdu3Zq166dAgIC/Hr8\n+PIXAJ/CZJ4QAAAMdklEQVTgcrm0d+9eZWVladeuXbpw4YJmzJihO++8U82bN7c6Hmpx8uRJZWVl\nKTs7W61atVJeXp6Sk5P97otfEsUMwAdVVFTom2++UVZWlr755hutXLnS6ki4AUeOHNH27dv1f//3\nf4qMjNTLL79sdSSPopgBeL2zZ8/q7NmzV82vfPz4cbVo0UKtW7e2KBluhsvl0vfff6+ePXtaHcWj\nOMcMVCkrK7vuutOnT3swCW7UqlWrdPbs2auWl5aW6s9//rMFiXAj1q5dq88///yq5X//+9+1a9cu\nCxJZi2IGqsyfP1/Z2dnVljkcDq1bt06LFy+2KBXq4vTp09fcq+rRo4eOHTtmQSLciO+++0733Xff\nVcuHDRum3NxcCxJZi2IGqiQlJWnr1q16+eWXdfr0aeXk5GjevHkqLy/X66+/bnU81ODixYvXXccN\nLcxXUVFxzW9hBwQE+OV90rk4E6gSHR2thQsXasOGDXruuecUFhampKQkdejQwepoqEV0dLRyc3N1\n5513Vlu+a9cutW3b1qJUqKugoCCdOnVKt956a7Xlp06dUlBQkEWprMOXv4AqlZWV2rBhgzIyMjR6\n9Gjl5uaqrKxMU6ZMUUxMjNXxUINTp07plVdeUffu3d331D58+LD+8Y9/aMGCBYyf4Xbt2qVVq1bp\n0UcfrTZ+6enpmjBhwlUfuHwdxQxUmTt3rnr27KmxY8e67zC1c+dOvf/++xowYIDGjRtncULUpLy8\nXNu3b9fx48clSe3bt9fdd9/tl3tc3ujYsWPasGGDe/w6dOigUaNGqWPHjhYn8zyKGahy5MgR96f1\nKzkcDq1fv949dzYANCaKGQBgqZrux2yz2TRz5kwPprEeX/4CqmRkZKi0tFSjRo2SJE2fPl1lZWVy\nuVxKTEzUAw88YHFCwDdd6xxyUVGRPvnkEzmdTgsSWYtiBqp8/vnnWrhwoftxaGio3n33XTkcDi1e\nvJhiNlhZWdl158M+ffq0oqOjPZwIN2LgwIHun/Pz85WWlqbvv/9eCQkJuvfeey1MZg2KGajicrnU\nqlUr9+PLfyyCgoLkcDisioU6mD9/vsaOHavBgwe7lzkcDn344YfKysrSH/7wBwvToS5Onjyp9evX\n64cfftCoUaM0depUNWnSxOpYlqCYgSrnz5+v9viRRx6RJDmdTp07d86KSKijpKQkrVq1ShkZGZoy\nZYqOHz+utWvXqn///kwO4wWWLl2qI0eOaOTIkZo4caICAgKqTRoTHBxsYTrP48tfQJUVK1YoODj4\nqnsvr1u3TmfPntW0adMsSoa62rBhg/7yl78wOYyXmTVrlvtnm81WbbYvm82mt99+24pYlqGYgSpl\nZWVavny5Dh8+rE6dOkmSjh49qttuu03Tp09XixYtLE6I62FyGPgSihn4mfz8/GqTVPDFIfMxOYz3\nq6io0FdffVVtgpG7775bTZs2tTiZ51HMQJVt27bpl7/8pSRp//79uuOOO9zrNm/erIceesiqaKgF\nk8N4txMnTujVV1/V7bff7h7HI0eO6MCBA3r++ef97pQEd5cCqnzyySfun1evXl1t3ZdffunpOLgB\n1ypl6dI36ill861atUpTp07V008/rREjRmjEiBF6+umnNXXqVK1atcrqeB5HMQNVrjx49PMDSRxY\nMltGRoY2bNjgfjx9+nRNmDBBv/nNb/TZZ59ZmAx1YbfbFRsbe9Xy2NhY/fjjjxYkshbFDFS58n6w\nP7837LXuFQtzfP7557rnnnvcj0NDQ7VmzRqtWLFCWVlZFiZDXbhcLpWXl1+13OFwqLKy0oJE1uI6\nZqDKyZMnNW/ePLlcLuXn52vevHmSLv3RKCgosDgdasLkMN7tl7/8pZKTkzV58mRFRUVJkgoKCrR6\n9Wr39z78CcUMVElJSbE6AuqJyWG826OPPqrNmzdr0aJFcjgccrlcat68uR5++GENHz7c6ngex7ey\ngVo4nU5lZWVpyJAhVkfBdTA5jO+4POOXP88bQDEDVS5cuKAtW7bIbrcrLi5OsbGx2rx5szZu3KhO\nnTrp+eeftzoiroPJYbxbZmZmjevj4+M9lMQMFDNQ5bXXXlPLli3VvXt37dmzR2fPnpXL5dKkSZPU\nuXNnq+OhDpgcxjtd75KoHTt2yG63a926dR5OZC2KGagyd+5cJScnS7p0+HratGlKTU1VUFCQxclQ\nGyaH8R0ul0tfffWVPvroI7Vv316PPPKI+yiIv+ByKaBKYOC/vgsZEBCgyMhIStlLMDmM96usrNQX\nX3yh2bNna8+ePZo7d65mz57td6Us8a1swO2HH37QhAkTJF361O5wODRhwgS5XC7ZbDatWbPG4oS4\nHiaH8W6bN2/Wpk2b1Lt3by1cuFBt2rSxOpKlKGagyl//+lerI6CemBzGu61evVohISHav3+/Dhw4\n4F5++UPxG2+8YWE6z+McMwCvl5iYqOjoaPfkMJe/9HV5cpi1a9danBA1KSwsrHH95UlH/AXFDMDr\n8YcdvoRD2QC83vWK9/LkMBQzvAnFDMDr1TY5DLO2wZtwKBuA12NyGO9WVlam5s2bX3Pd6dOn/W6i\nGK5jBuD18vPzNWvWLN1///167rnndOLECSUlJVHKXmL+/PnKzs6utszhcGjdunVavHixRamsQzED\n8HpMDuPdkpKStHXrVr388ss6ffq0cnJyNG/ePJWXl+v111+3Op7HcSgbgNd74okn3IdCL08O06xZ\nMyaH8TIbNmzQX/7yF4WFhSkpKUkdOnSwOpIlKGYAgKUqKyu1YcMGZWRkaPTo0crNzVVZWZmmTJmi\nmJgYq+N5HMUMALDU3Llz1bNnT40dO1a33HKLJGnnzp16//33NWDAAI0bN87ihJ5FMQMALHXkyBF1\n6dLlquUOh0Pr16/X2LFjLUhlHYoZAACDMMEIAMBSGRkZKi0t1ahRoyRJ06dPV1lZmVwulxITE/XA\nAw9YnNCzuFwKAGCpzz//XPfcc4/7cWhoqNasWaMVK1YoKyvLwmTWoJgBAJZyuVxq1aqV+/HAgQMl\nSUFBQXI4HFbFsgzFDACw1Pnz56s9fuSRRyRdugnJuXPnrIhkKYoZAGCpvn37at26dVct/+CDDxQb\nG2tBImvxrWwAgKXKysq0fPlyHT58WJ06dZIkHT16VLfddpumT5+uFi1aWJzQsyhmAIAR8vPzdfz4\ncUlS+/bt/e6uUpdxKBsAYKlt27ZJktq2bavg4GDFxcW5S3nz5s1WRrMExQwAsNQnn3zi/nn16tXV\n1n355ZeejmM5ihkAYKkrz6j+/OyqP55tpZgBAJay2WzX/Plaj/0BX/4CAFgqMTFR0dHRcrlcys/P\nd59fdrlcKigo0Nq1ay1O6FkUMwDAUoWFhTWuj4qK8lASM3ATCwCApa5XvE6nU1lZWRQzAACedOHC\nBW3ZskV2u11xcXGKjY3V5s2btXHjRnXq1ElDhgyxOqJHcSgbAGCp1157TS1btlT37t21Z88enT17\nVi6XS5MmTVLnzp2tjudx7DEDACyVn5+v5ORkSdKwYcM0bdo0paamKigoyOJk1uByKQCApQID/7WP\nGBAQoMjISL8tZYlD2QAAiz3xxBNq3ry5pEuXSDkcDjVr1kwul0s2m01r1qyxOKFnUcwAABiEQ9kA\nABiEYgYAwCAUMwAABqGYAQAwCNcxAz5g/Pjx7p8dDocCAwMVEHDpc/fkyZP1pz/9SUuXLlVISIgk\nqby8XM8//7yGDx+ufv366emnn1azZs0kSSEhIbr//vuVkJAgSRozZox73WWPPfaYRo8e7YlfDfA7\nFDPgA668+86sWbM0ffp0xcbGupd9//33eu+99/Tss89KktavX6+wsDDdf//97hsIvPfee2rSpIkO\nHjyo//7v/1bnzp3Vr18/SdLrr7/uvuMPgMbFoWzAD0yYMEF79+5Vbm6ujh07pi1btmjGjBnXvNdt\n9+7d1aFDBx07dsyCpADYYwb8wC233KKpU6fqj3/8o8LCwvTYY4+pbdu2Vz3P5XLpwIEDOn78uMaN\nG2dBUgAUM+An4uLitG3bNhUUFGj48OFXrZ88ebJsNpvCwsI0btw49enTx71uwYIF1faun3vuOfdh\nbgANi2IG/Ej79u2rfTHsSitXrlSTJk2u+e9effVVzjEDHsI5ZgAADEIxAwBgEA5lA6jV/Pnzqz0e\nNmyYJk6caE0YwMdxdykAAAzCoWwAAAxCMQMAYBCKGQAAg1DMAAAYhGIGAMAgFDMAAAahmAEAMAjF\nDACAQShmAAAM8v+dd/Ng/hF+2QAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<matplotlib.figure.Figure at 0x7f589c002208>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%sql -pb SELECT * FROM RESULTS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "Every system will have different performance\n",
    "characteristics, but the odds are that the LIKE statement\n",
    "was faster than the regular expression. The performance is\n",
    "probably ranked this way (with tx/sec being the measurement):\n",
    "\n",
    "<pre>\n",
    "LIKE > REGEXP(Non-capturing) > REGEXP(Capturing) > REGEXP(String) \n",
    "</pre>\n",
    "\n",
    "However, your system will probably get different results. So\n",
    "while Regular expressions are very powerful, there is a\n",
    "performance penalty when using them. In addition, you may\n",
    "need to consider the use of indexes from a performance\n",
    "perspective.\n",
    "\n",
    "The following SQL will add an index to the STATION name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {
    "collapsed": true,
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [],
   "source": [
    "%%sql -q\n",
    "DROP INDEX TEMP_STATION_INDEX;\n",
    "CREATE INDEX TEMP_STATION_INDEX ON TEMP_LINE(STATION);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will try the 4 queries again and plot the result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total iterations in 1 second(s): 180\n",
      "Total iterations in 1 second(s): 292\n",
      "Total iterations in 1 second(s): 280\n",
      "Total iterations in 1 second(s): 275\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<matplotlib.figure.Figure at 0x7f589bf98550>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAeYAAAG1CAYAAAAsiVeWAAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAALEgAACxIB0t1+/AAAIABJREFUeJzt3Xt4zHeix/HPDOIWuWlIQ7BurVuax8bj0lrturRUyaaq\nh0Ox7tX2cW+XXd3n4Om6hNUtR591Wezuo/bYpI5uY61UVHKeNhV6UxysuieYEYKcyWXOH2JWKjIR\nyfy+k7xff83v95vJfCY/8pnf7fuzud1utwAAgBHsVgcAAAD/QjEDAGAQihkAAINQzAAAGIRiBgDA\nIBQzAAAGoZgBADBIbasD3HH+/HmrI1SZyMjIav35qjvWn/9i3fm36r7+IiMjS53PFjMAAAbxusXs\ncrn09ttvq6CgQIWFherRo4eGDx+u3NxcrVy5UpcuXVJ4eLhmzJihwMBASVJiYqJSUlJkt9s1btw4\nxcTEVPkHAQCgOvBazHXq1NHbb7+tevXqqaCgQAsWLFBMTIw+//xzdenSRXFxcUpKSlJSUpJGjRql\ns2fPKj09XStWrJDT6dTChQu1atUq2e1snAMA4I3XtrTZbKpXr54kqbCwUIWFhbLZbMrIyFCfPn0k\nSX369FFGRoYkKSMjQ7169VKdOnXUpEkTRURE6Pjx41X4EQAAqD7KdfJXUVGR3nzzTV28eFHPPvus\n2rVrp5ycHIWGhkqSQkJClJOTI0lyOBxq166d57VhYWFyOBxe3+N+B8Gri+r++ao71p//Yt35t5q4\n/spVzHa7XcuWLdONGze0fPlynT59usRym80mm832UEGq+5l31fnzVXesP//FuvNv1X39VcpZ2Q0b\nNlSnTp106NAhBQcHy+l0SpKcTqeCgoIk3d5CvnLliuc1DodDYWFhFc0NAECN4nWL+dq1a6pVq5Ya\nNmwol8ulr776SkOHDlVsbKxSU1MVFxen1NRUdevWTZIUGxurd999V4MHD5bT6dSFCxfUtm3bKv8g\nAICap2/fvvrRj36kwsJCPfroo5o3b54CAwN18eJFjRkzRlFRUZ7nvvTSS3r22Wf1t7/9Tf/1X/8l\nm82moqIijR8/Xk899ZSmT5+uqVOn6rHHHpMkXbx4Ub/4xS+0ceNGHTp0SB988IHeeecdz8+bP3++\nsrKydOvWLeXk5CgiIkKSNHPmTHXs2LHCn8lrMTudTq1evVpFRUVyu93q2bOnfvzjH6t9+/ZauXKl\nUlJSPJdLSVJUVJR69uypmTNnym63a/z48ZyRDQA1QOHEIZX7Az/6wutTAgICtG7dOknSO++847lC\nSLq9q/jOsjsuXbqkP/3pT3r//fcVGBioW7du6erVqxWKt3jxYknSgQMHlJiYqEWLFlXo5/yQ12Ju\n2bKlli5des/8Ro0aacGCBaW+Jj4+XvHx8Q+fDgCAcurUqZNOnDhR5nOcTqfq16+v+vXrS1KJx6Yw\nZkhOAAAqqrCwUJmZmRo0aJBn3vnz5zVhwgTP9BtvvKFOnTopNDRUI0eOVNeuXdW7d2/16tXLisj3\nRTEDAPyWy+XShAkTdPnyZbVs2VI//vGPPctK25UtSUuXLtWRI0eUmZmp1atX69ixYxo7dmypVxc9\n7BVHFUExw+9U+nEsL8749N2kWr/f4eN3BPzXnWPMeXl5mjt3rpKSkvTiiy+W+RqbzaYOHTqoQ4cO\nio2N1ZIlSzR27FgFBQXp+vXrnuddu3ZNwcHBVf0R7sFZWQAAv1evXj29/vrr+stf/qLCwsL7Pu/y\n5cs6duyYZ/r48eNq2rSpJCkmJka7d++W2+2WJO3atcuSez2wxQwAqBbatWun1q1ba8+ePYqOjr7n\nGPPAgQP15JNPau3atbp8+bICAgIUEhLiuapo8ODBOn36tOc1jz32mCZOnOh5fWZmpl566SXP9K9/\n/Wt16tSp0j+HzX3nq4HFqvvoLtX58/mar3dl+xq7sisP//f8W3Vff9yPGQAAP0AxAwBgEIoZAACD\nUMwAABiEYgYAwCAUMwAABqGYAQAwCMUMAIBBGPkLgM8wzjngHVvMAAAYhGIGAMAgFDMAAAahmAEA\nMAgnfwEAyoWT93yDLWYAAAxCMQMAYBCKGQAAg1DMAAAYhGIGAMAgFDMAAAahmAEAMAjFDACAQShm\nAAAMQjEDAGAQihkAAINQzAAAGIRiBgDAIBQzAAAGoZgBADAIxQwAgEEoZgAADEIxAwBgEIoZAACD\n1Pb2hMuXL2v16tW6evWqbDab+vXrp0GDBmnbtm3as2ePgoKCJEkjRoxQ165dJUmJiYlKSUmR3W7X\nuHHjFBMTU7WfAgCAasJrMdeqVUujR49W69atdevWLb311luKjo6WJD3//PMaMmRIieefPXtW6enp\nWrFihZxOpxYuXKhVq1bJbmfjHAAAb7y2ZWhoqFq3bi1Jql+/vpo1ayaHw3Hf52dkZKhXr16qU6eO\nmjRpooiICB0/frzyEgMAUI090GZsdna2/vnPf6pt27aSpOTkZM2ePVtr1qxRbm6uJMnhcKhx48ae\n14SFhZVZ5AAA4F+87sq+Iy8vTwkJCRo7dqwaNGigAQMGaNiwYZKkDz74QJs3b9arr75a4SCRkZEV\nfq0/qO6fz5fOWB2gilXnfyusO//G+vONchVzQUGBEhIS1Lt3b3Xv3l2SFBIS4lnet29fLVmyRNLt\nLeQrV654ljkcDoWFhXl9j/Pnzz9QcH8SGRlZrT8fKhf/VvwX686/+Xr93e+LgNdd2W63W2vXrlWz\nZs00ePBgz3yn0+l5/PnnnysqKkqSFBsbq/T0dOXn5ys7O1sXLlzw7PoGAABl87rFfPToUe3bt08t\nWrTQnDlzJN2+NCotLU2nTp2SzWZTeHi4Jk2aJEmKiopSz549NXPmTNntdo0fP54zsgEAKCevxfz4\n449r27Zt98y/c81yaeLj4xUfH/9wyQAAqIHYlAUAwCAUMwAABqGYAQAwCMUMAIBBKGYAAAxCMQMA\nYBCKGQAAg1DMAAAYhGIGAMAgFDMAAAahmAEAMAjFDACAQShmAAAMQjEDAGAQihkAAINQzAAAGIRi\nBgDAIBQzAAAGoZgBADAIxQwAgEEoZgAADEIxAwBgEIoZAACDUMwAABiEYgYAwCAUMwAABqGYAQAw\nCMUMAIBBKGYAAAxCMQMAYBCKGQAAg1DMAAAYhGIGAMAgFDMAAAahmAEAMAjFDACAQShmAAAMQjED\nAGAQihkAAINQzAAAGKS2tydcvnxZq1ev1tWrV2Wz2dSvXz8NGjRIubm5WrlypS5duqTw8HDNmDFD\ngYGBkqTExESlpKTIbrdr3LhxiomJqfIPAgBAdeC1mGvVqqXRo0erdevWunXrlt566y1FR0dr7969\n6tKli+Li4pSUlKSkpCSNGjVKZ8+eVXp6ulasWCGn06mFCxdq1apVstvZOAcAwBuvbRkaGqrWrVtL\nkurXr69mzZrJ4XAoIyNDffr0kST16dNHGRkZkqSMjAz16tVLderUUZMmTRQREaHjx49X4UcAAKD6\neKDN2OzsbP3zn/9U27ZtlZOTo9DQUElSSEiIcnJyJEkOh0ONGzf2vCYsLEwOh6MSIwMAUH153ZV9\nR15enhISEjR27Fg1aNCgxDKbzSabzfZQQSIjIx/q9aar7p/Pl85YHaCKVed/K6w7/8b6841yFXNB\nQYESEhLUu3dvde/eXZIUHBwsp9Op0NBQOZ1OBQUFSbq9hXzlyhXPax0Oh8LCwry+x/nz5yuS3y9E\nRkZW68+HysW/Ff/FuvNvvl5/9/si4HVXttvt1tq1a9WsWTMNHjzYMz82NlapqamSpNTUVHXr1s0z\nPz09Xfn5+crOztaFCxfUtm3byvgMAABUe163mI8ePap9+/apRYsWmjNnjiRpxIgRiouL08qVK5WS\nkuK5XEqSoqKi1LNnT82cOVN2u13jx4/njGwAAMrJazE//vjj2rZtW6nLFixYUOr8+Ph4xcfHP1wy\nAABqIDZlAQAwCMUMAIBBKGYAAAxCMQMAYBCKGQAAg1DMAAAYhGIGAMAgFDMAAAahmAEAMAjFDACA\nQShmAAAMQjEDAGAQihkAAINQzAAAGMTrbR+ro8KJQ3z6fmd8+m5Srd/v8PE7AgAqC1vMAAAYhGIG\nAMAgFDMAAAahmAEAMAjFDACAQShmAAAMQjEDAGAQihkAAINQzAAAGIRiBgDAIBQzAAAGoZgBADAI\nxQwAgEEoZgAADEIxAwBgEIoZAACDUMwAABiEYgYAwCAUMwAABqGYAQAwCMUMAIBBKGYAAAxCMQMA\nYBCKGQAAg1DMAAAYpLa3J6xZs0aZmZkKDg5WQkKCJGnbtm3as2ePgoKCJEkjRoxQ165dJUmJiYlK\nSUmR3W7XuHHjFBMTU4XxAQCoXrwW89NPP63nnntOq1evLjH/+eef15AhQ0rMO3v2rNLT07VixQo5\nnU4tXLhQq1atkt3OhjkAAOXhtTE7duyowMDAcv2wjIwM9erVS3Xq1FGTJk0UERGh48ePP3RIAABq\nCq9bzPeTnJysffv2qXXr1nrllVcUGBgoh8Ohdu3aeZ4TFhYmh8NRKUEBAKgJKlTMAwYM0LBhwyRJ\nH3zwgTZv3qxXX331oYJERkY+1OsfxBmfvZM1fPm7tALrz3+x7vwb6883KlTMISEhnsd9+/bVkiVL\nJN3eQr5y5YpnmcPhUFhYWLl+5vnz5ysSBaXgd+nfWH/+i3Xn33y9/u73RaBCZ2U5nU7P488//1xR\nUVGSpNjYWKWnpys/P1/Z2dm6cOGC2rZtW5G3AACgRvK6xfzb3/5Whw8f1vXr1zVlyhQNHz5c3377\nrU6dOiWbzabw8HBNmjRJkhQVFaWePXtq5syZstvtGj9+PGdkAwDwALwW8/Tp0++Z99Of/vS+z4+P\nj1d8fPzDpQIAoIZicxYAAINQzAAAGIRiBgDAIBQzAAAGoZgBADAIxQwAgEEoZgAADEIxAwBgEIoZ\nAACDUMwAABiEYgYAwCAUMwAABqGYAQAwCMUMAIBBKGYAAAxCMQMAYBCKGQAAg1DMAAAYhGIGAMAg\nFDMAAAahmAEAMAjFDACAQShmAAAMQjEDAGAQihkAAINQzAAAGIRiBgDAIBQzAAAGoZgBADAIxQwA\ngEEoZgAADEIxAwBgEIoZAACDUMwAABiEYgYAwCAUMwAABqGYAQAwCMUMAIBBKGYAAAxCMQMAYJDa\n3p6wZs0aZWZmKjg4WAkJCZKk3NxcrVy5UpcuXVJ4eLhmzJihwMBASVJiYqJSUlJkt9s1btw4xcTE\nVO0nAACgGvG6xfz0009r3rx5JeYlJSWpS5cuevfdd9WlSxclJSVJks6ePav09HStWLFC8+fP1/r1\n61VUVFQ1yQEAqIa8FnPHjh09W8N3ZGRkqE+fPpKkPn36KCMjwzO/V69eqlOnjpo0aaKIiAgdP368\nCmIDAFA9ed2VXZqcnByFhoZKkkJCQpSTkyNJcjgcateuned5YWFhcjgc5fqZkZGRFYlSIWd89k7W\n8OXv0gqsP//FuvNvrD/fqFAx381ms8lmsz10kPPnzz/0z8Bt/C79G+vPf7Hu/Juv19/9vghU6Kzs\n4OBgOZ1OSZLT6VRQUJCk21vIV65c8TzP4XAoLCysIm8BAECNVKFijo2NVWpqqiQpNTVV3bp188xP\nT09Xfn6+srOzdeHCBbVt27by0gIAUM153ZX929/+VocPH9b169c1ZcoUDR8+XHFxcVq5cqVSUlI8\nl0tJUlRUlHr27KmZM2fKbrdr/Pjxstu5VBoAgPLyWszTp08vdf6CBQtKnR8fH6/4+PiHSwUAQA3F\n5iwAAAahmAEAMAjFDACAQShmAAAMQjEDAGAQihkAAINQzAAAGIRiBgDAIBQzAAAGoZgBADAIxQwA\ngEEoZgAADEIxAwBgEIoZAACDUMwAABiEYgYAwCAUMwAABqGYAQAwCMUMAIBBKGYAAAxCMQMAYBCK\nGQAAg1DMAAAYhGIGAMAgFDMAAAahmAEAMAjFDACAQShmAAAMQjEDAGAQihkAAINQzAAAGIRiBgDA\nIBQzAAAGoZgBADAIxQwAgEEoZgAADEIxAwBgEIoZAACDUMwAABik9sO8eNq0aapXr57sdrtq1aql\n3/zmN8rNzdXKlSt16dIlhYeHa8aMGQoMDKysvAAAVGsPVcyS9PbbbysoKMgznZSUpC5duiguLk5J\nSUlKSkrSqFGjHvZtAACoESp9V3ZGRob69OkjSerTp48yMjIq+y0AAKi2HnqLeeHChbLb7erfv7/6\n9eunnJwchYaGSpJCQkKUk5Pz0CEBAKgpHqqYFy5cqLCwMOXk5GjRokWKjIwssdxms8lms5XrZ/3w\ntVXpjM/eyRq+/F1agfXnv1h3/o315xsPVcxhYWGSpODgYHXr1k3Hjx9XcHCwnE6nQkND5XQ6Sxx/\nLsv58+cfJgruwu/Sv7H+/Bfrzr/5ev3d74tAhY8x5+Xl6datW57HX331lVq0aKHY2FilpqZKklJT\nU9WtW7eKvgUAADVOhbeYc3JytHz5cklSYWGhnnrqKcXExKhNmzZauXKlUlJSPJdLAQCA8qlwMTdt\n2lTLli27Z36jRo20YMGChwoFAEBNxchfAAAYhGIGAMAgFDMAAAahmAEAMAjFDACAQShmAAAMQjED\nAGAQihkAAINQzAAAGIRiBgDAIBQzAAAGoZgBADAIxQwAgEEoZgAADEIxAwBgEIoZAACDUMwAABiE\nYgYAwCAUMwAABqGYAQAwCMUMAIBBKGYAAAxCMQMAYBCKGQAAg1DMAAAYhGIGAMAgFDMAAAahmAEA\nMAjFDACAQShmAAAMQjEDAGAQihkAAINQzAAAGIRiBgDAIBQzAAAGoZgBADAIxQwAgEEoZgAADEIx\nAwBgEIoZAACD1K6qH3zo0CFt3LhRRUVF6tu3r+Li4qrqrQAAqDaqZIu5qKhI69ev17x587Ry5Uql\npaXp7NmzVfFWAABUK1VSzMePH1dERISaNm2q2rVrq1evXsrIyKiKtwIAoFqpkmJ2OBxq3LixZ7px\n48ZyOBxV8VYAAFQrVXaM+UFFRkb67s0++sJ374XKx/rzX6w7/8b684kq2WIOCwvTlStXPNNXrlxR\nWFhYVbwVAADVSpUUc5s2bXThwgVlZ2eroKBA6enpio2NrYq3AgCgWrG53W53VfzgzMxMbdq0SUVF\nRXrmmWcUHx9fFW8DAEC1UmXFDAAAHhwjfwEAYBCKGQAAg1DMAAAYhGIGihUVFSkvL88zfezYMR0+\nfFiHDx/WrVu3LEwGb/bt2+d5fOTIkRLLkpOTfR0HD+jcuXOex/n5+SWWHTt2zNdxLEcxVzL+QPiv\nP/7xj9q1a5dnetWqVdqxY4e2b9+u7du3W5gM3nz00Ueexxs3biyx7JNPPvF1HDygd9991/P4l7/8\nZYll69ev93Ucy1HMlYw/EP7rm2++0eDBgz3TDRs21FtvvaVf/vKXOnr0qIXJ4M3dF5f88EITLjwx\nH+uvJIq5kvEPzH+53W7VqlXLM/3v//7vkiSbzVZiFzfMY7PZSn1c2jTMw/oryZixsqsL/oH5r4KC\nAt26dUv169eXJD3xxBOSpJs3b95z3AtmOXfunGbPni23262srCzNnj1b0u0vW9nZ2RangzdXrlzR\nhg0b7nksqUbeAIkBRirZqFGjFBER4fkDERERIelffyC2bNlicULcz86dO/X1119r4sSJeuSRRyRJ\nly5d0rp169SpUycNGTLE4oS4n0uXLpW5PDw83EdJUBF79+4tc/nTTz/tkxymoJgrWVl/INxut5o0\naeLDNHhQf//735WYmKj/+7//k9vtVv369RUXF6cBAwZYHQ0VUFRUpLS0NPXu3dvqKKigy5cve74o\n1xQUcyXbu3dvqd/uCgsL9bvf/U7Tp0/3fSg8sDuXR93ZrQ2z3bx5U7t27ZLD4VBsbKyio6OVnJys\nnTt3qmXLlpo7d67VEeHFsWPH5HA41KFDBwUHB+v7779XUlKSjhw5ov/8z/+0Op5PcYy5kn388ccq\nKChQv379PPPy8vK0fPlyNW7c2MJk8CY1NbXM5X369PFREjyo9957Tw0bNlT79u21Z88eJSYmyu12\na86cOWrVqpXV8eDFli1blJmZqZYtW+rDDz/UE088oT179uhnP/uZpk6danU8n6OYK9mvfvUrLV68\nWC6XS4MGDdK1a9f0zjvvqHPnzp6zfGGmEydOlDr/iy++kMPhoJgNlpWVpYSEBElS3759NWnSJK1Z\ns0YBAQEWJ0N5ZGZmasmSJQoICFBubq6mTp2qhISEGnvoj2KuZIGBgfrVr36ld955R06nU1988YX6\n9++vQYMGWR0NXvz85z/3PHa73fr000/14Ycfql27dty21HC1a//rT5ndblfjxo0pZT8SEBDgWV+B\ngYF69NFHa2wpSxxjrnSfffaZpNvHKDdv3qzOnTvrySef9Czv3r27VdFQDoWFhdq7d6/++7//W+3a\ntdPPfvYzRUZGWh0LXrz88suqV6+epNtfqlwul+rWrSu32y2bzaZNmzZZnBBlGTt2rDp06OCZ/u67\n70pMv/nmm1bEsgzFXMnWrFlT5vJXX33VR0nwoJKTk/Xxxx+rc+fOGjp0aI3+xg740uHDh8tc3rFj\nRx8lMQPFDBR7+eWXFRQUpKCgoBKDwdzZ6lq+fLmF6VCW3Nzce+Y1bNiQQX3glyjmSrZz584yl989\nFjPMwiAV/mvatGmy2Wwlhr3Ny8tTq1atNHnyZPZ+GG7WrFn3jJrYqFEjderUSS+88EKNO1+AYq5k\nf/nLX+67zGazadiwYT5MA9Rsn332mf7xj39o/vz5VkdBGUr7Upybm6vU1FTl5eVpypQpFqSyDmdl\nV7KXXnrpvsvuvvMUzPPKK6+UuuuTE4j8V/fu3fXXv/7V6hjworS9UeHh4frRj35UIweHoZh9aOfO\nnXr++eetjoH72Lx5s9URUMny8vJUVFRkdQw8hJq4U5diBry4ceOGdu3axbXMBivt3I7c3FwdOHBA\nzz77rAWJ8CBOnjx5z7wbN27o008/LXHZVE1BMQPFLl++rO3bt8vpdKpbt2568skntW3bNqWmpuqp\np56yOh7KcGds8ztsNptCQkL0+uuvq0WLFhalQnmVdte9Ro0aqWPHjiWGN64pKOZKVtZxSpfLZUEi\nlNfq1avVoUMHde/eXYcOHdIvfvELtWzZUgkJCQoJCbE6HsqQn5+vkSNHWh0DFfTss8+qR48eVscw\nBsVcyThO6b9yc3M1fPhwSVJMTIymTJmiN954Q3a73eJk8ObLL7+kmP1YYmIixXwXihm4y90DVTRq\n1Eg3b970TAcGBloRCeVQVFRU6iAjd7Du4E+4jhkoVtogFXfYbDa99957FqRCeYwcOVJhYWGsOz81\natQoRURE3DO/po66xxYzUOzXv/41o3v5qebNm2vp0qVWx0AFNWnSpMbdqKIsFDNQbPny5VqyZInV\nMYAap3bt2nwpvgtntQDFOKrjvwYNGqRr167pxIkTunHjhtVx8IAee+wxqyMYhS1moJjD4dCGDRvu\nu/znP/+5D9PgQRQWFmrmzJlq2rSpsrOzNXnyZMXGxlodC+XUu3dvzZkzRxcvXlSLFi00depUNW/e\n3OpYlqGYgWIBAQFq3bq11TFQAX/729+0YsUKBQUFKSsrS++++y7F7Ec2bNig0aNHq0OHDvriiy+0\nadOmGn3jEYoZKNaoUSM9/fTTVsdABdSuXVtBQUGSpKZNm6qgoMDiRHgQRUVFio6OliT17NlTSUlJ\nFieyFsUMFKtdm/8O/urKlSslDkP8cJrDEGa7efOmPvvsM8/0jRs3Skx3797diliW4TpmoFh2drYC\nAwPVoEEDSdI333yjjIwMhYeH67nnnqO4DbZ3794yl7MnxGxr1qwpc/mrr77qoyRmoJiBYvPmzdPs\n2bMVFhamU6dOaeHChYqLi9P333+v2rVr17ibtVcHLpdLBw4cUM+ePa2Oggq6evVqjRurnk0AoJjL\n5VJYWJgkad++fXrmmWf0wgsvqKioqEberN1fFRUV6dChQ0pLS9NXX32lxx9/nGL2M3d2Ze/fv1/n\nzp3T+++/b3Ukn6KYgWJ37zz69ttvNWLECEniJhZ+4vDhw9q/f78OHjyoNm3a6OjRo3rvvfdUt25d\nq6OhHFwulzIyMrR//36dOnVKt27d0pw5c7gfM1CTde7cWStWrFBoaKhyc3PVuXNnSZLT6eT4suGm\nTJmiRx55RAMGDNDo0aNVv359TZs2jVL2E6tWrdKRI0cUHR2tgQMHqnPnznr99dfVqVMnq6NZgk0B\noNjYsWPVvXt3hYeHa+HChZ4yvnr1qv7t3/7N4nQoS48ePeR0OpWenq4DBw4oLy+v1Puiw0xnz55V\nw4YN1axZMzVr1kx2u71Grz9O/gJQLbjdbn377bdKS0vTwYMHdfPmTU2ZMkVdu3ZVvXr1rI4HL86d\nO6e0tDSlp6erUaNGOn/+vBISEmrciV8SxQygGiooKNCXX36ptLQ0ffnll1q/fr3VkfAATp48qf37\n9+t//ud/1LhxYy1atMjqSD5FMQPwe9euXdO1a9fuGV/5zJkzql+/vh555BGLkuFhuN1ufffdd+rY\nsaPVUXyKY8xAsby8vPsuu3jxog+T4EFt2LBB165du2d+bm6u/vSnP1mQCA9iy5Yt2r179z3z//GP\nf+jgwYMWJLIWxQwUmzNnjtLT00vMc7lc2rp1qxYvXmxRKpTHxYsXS92q6tChg06fPm1BIjyIb775\nRv369btnft++fZWZmWlBImtRzECx+fPna+/evVq0aJEuXryojIwMzZ49W/n5+Vq2bJnV8VCGW7du\n3XcZN7QwX0FBQalnYdvt9hp5n3QuzgSKRUREaN68edqxY4emT5+ukJAQzZ8/X1FRUVZHgxcRERHK\nzMxU165dS8w/ePCgmjZtalEqlFdAQIAuXLigRx99tMT8CxcuKCAgwKJU1uHkL6BYYWGhduzYoZSU\nFA0dOlSZmZnKy8vThAkTFBkZaXU8lOHChQv6zW9+o/bt23vuqX3ixAn97//+r958803Wn+EOHjyo\nDRs26MVqniD7AAAKfUlEQVQXXyyx/pKSkjRmzJh7vnBVdxQzUGzWrFnq2LGjRowY4bnD1IEDB7R5\n82Z1795dI0eOtDghypKfn6/9+/frzJkzkqTmzZvrqaeeqpFbXP7o9OnT2rFjh2f9RUVFaciQIWrR\nooXFyXyPYgaKnTx50vNt/W4ul0vbt2/3jJ0NAFWJYgYAWKqs+zHbbDZNnTrVh2msx8lfQLGUlBTl\n5uZqyJAhkqTJkycrLy9Pbrdbo0aN0oABAyxOCFRPpR1DvnLlij766CMVFRVZkMhaFDNQbPfu3Zo3\nb55nOjg4WO+//75cLpcWL15MMRssLy/vvuNhX7x4URERET5OhAfRo0cPz+OsrCwlJibqu+++U1xc\nnH76059amMwaFDNQzO12q1GjRp7pO38sAgIC5HK5rIqFcpgzZ45GjBihXr16eea5XC799a9/VVpa\nmn73u99ZmA7lce7cOW3fvl2nTp3SkCFDNHHiRNWqVcvqWJagmIFiN27cKDEdHx8vSSoqKtL169et\niIRymj9/vjZs2KCUlBRNmDBBZ86c0ZYtW9StWzcGh/EDK1as0MmTJzV48GCNHTtWdru9xKAxgYGB\nFqbzPU7+AoqtW7dOgYGB99x7eevWrbp27ZomTZpkUTKU144dO/TnP/+ZwWH8zLRp0zyPbTZbidG+\nbDab3nvvPStiWYZiBorl5eVp7dq1OnHihFq2bClJ+v7779WmTRtNnjxZ9evXtzgh7ofBYVCdUMzA\nD2RlZZUYpIITh8zH4DD+r6CgQJ9++mmJAUaeeuop1alTx+JkvkcxA8X27dunn/zkJ5KkI0eO6PHH\nH/csS05O1nPPPWdVNHjB4DD+7ezZs1qyZIkee+wxz3o8efKkjh49qrlz59a4QxLcXQoo9tFHH3ke\nb9y4scSyTz75xNdx8ABKK2Xp9hn1lLL5NmzYoIkTJ+q1117ToEGDNGjQIL322muaOHGiNmzYYHU8\nn6OYgWJ37zz64Y4kdiyZLSUlRTt27PBMT548WWPGjNErr7yiv//97xYmQ3k4HA5FR0ffMz86OlpX\nr161IJG1KGag2N33g/3hvWFLu1cszLF7924988wznung4GBt2rRJ69atU1pamoXJUB5ut1v5+fn3\nzHe5XCosLLQgkbW4jhkodu7cOc2ePVtut1tZWVmaPXu2pNt/NLKzsy1Oh7IwOIx/+8lPfqKEhASN\nHz9e4eHhkqTs7Gxt3LjRc95HTUIxA8VWrlxpdQRUEIPD+LcXX3xRycnJWrBggVwul9xut+rVq6cX\nXnhBAwcOtDqez3FWNuBFUVGR0tLS1Lt3b6uj4D4YHKb6uDPiV00eN4BiBordvHlTu3btksPhUGxs\nrKKjo5WcnKydO3eqZcuWmjt3rtURcR8MDuPfUlNTy1zep08fHyUxA8UMFFu6dKkaNmyo9u3b6+uv\nv9a1a9fkdrs1btw4tWrVyup4KAcGh/FP97sk6osvvpDD4dDWrVt9nMhaFDNQbNasWUpISJB0e/f1\npEmTtGbNGgUEBFicDN4wOEz14Xa79emnn+rDDz9U8+bNFR8f79kLUlNwuRRQrHbtf50Labfb1bhx\nY0rZTzA4jP8rLCzUnj17NGPGDH399deaNWuWZsyYUeNKWeKsbMDj1KlTGjNmjKTb39pdLpfGjBkj\nt9stm82mTZs2WZwQ98PgMP4tOTlZH3/8sTp37qx58+apSZMmVkeyFMUMFPvggw+sjoAKYnAY/7Zx\n40YFBQXpyJEjOnr0qGf+nS/Fy5cvtzCd73GMGYDfGzVqlCIiIjyDw9w56evO4DBbtmyxOCHKcunS\npTKX3xl0pKagmAH4Pf6wozphVzYAv3e/4r0zOAzFDH9CMQPwe94Gh2HUNvgTdmUD8HsMDuPf8vLy\nVK9evVKXXbx4scYNFMN1zAD8XlZWlqZNm6b+/ftr+vTpOnv2rObPn08p+4k5c+YoPT29xDyXy6Wt\nW7dq8eLFFqWyDsUMwO8xOIx/mz9/vvbu3atFixbp4sWLysjI0OzZs5Wfn69ly5ZZHc/n2JUNwO+9\n/PLLnl2hdwaHqVu3LoPD+JkdO3boz3/+s0JCQjR//nxFRUVZHckSFDMAwFKFhYXasWOHUlJSNHTo\nUGVmZiovL08TJkxQZGSk1fF8jmIGAFhq1qxZ6tixo0aMGKEGDRpIkg4cOKDNmzere/fuGjlypMUJ\nfYtiBgBY6uTJk2rduvU9810ul7Zv364RI0ZYkMo6FDMAAAZhgBEAgKVSUlKUm5urIUOGSJImT56s\nvLw8ud1ujRo1SgMGDLA4oW9xuRQAwFK7d+/WM88845kODg7Wpk2btG7dOqWlpVmYzBoUMwDAUm63\nW40aNfJM9+jRQ5IUEBAgl8tlVSzLUMwAAEvduHGjxHR8fLyk2zchuX79uhWRLEUxAwAs9cQTT2jr\n1q33zN+2bZuio6MtSGQtzsoGAFgqLy9Pa9eu1YkTJ9SyZUtJ0vfff682bdpo8uTJql+/vsUJfYti\nBgAYISsrS2fOnJEkNW/evMbdVeoOdmUDACy1b98+SVLTpk0VGBio2NhYTyknJydbGc0SFDMAwFIf\nffSR5/HGjRtLLPvkk098HcdyFDMAwFJ3H1H94dHVmni0lWIGAFjKZrOV+ri06ZqAk78AAJYaNWqU\nIiIi5Ha7lZWV5Tm+7Ha7lZ2drS1btlic0LcoZgCApS5dulTm8vDwcB8lMQM3sQAAWOp+xVtUVKS0\ntDSKGQAAX7p586Z27dolh8Oh2NhYRUdHKzk5WTt37lTLli3Vu3dvqyP6FLuyAQCWWrp0qRo2bKj2\n7dvr66+/1rVr1+R2uzVu3Di1atXK6ng+xxYzAMBSWVlZSkhIkCT17dtXkyZN0po1axQQEGBxMmtw\nuRQAwFK1a/9rG9Fut6tx48Y1tpQldmUDACz28ssvq169epJuXyLlcrlUt25dud1u2Ww2bdq0yeKE\nvkUxAwBgEHZlAwBgEIoZAACDUMwAABiEYgYAwCBcxwxUA6NHj/Y8drlcql27tuz229+7x48frz/+\n8Y9asWKFgoKCJEn5+fmaO3euBg4cqJiYGL322muqW7euJCkoKEj9+/dXXFycJGn48OGeZXcMGzZM\nQ4cO9cVHA2ocihmoBu6++860adM0efJkRUdHe+Z99913+sMf/qA33nhDkrR9+3aFhISof//+nhsI\n/OEPf1CtWrV07Ngx/cd//IdatWqlmJgYSdKyZcs8d/wBULXYlQ3UAGPGjNG3336rzMxMnT59Wrt2\n7dKUKVNKvddt+/btFRUVpdOnT1uQFABbzEAN0KBBA02cOFG///3vFRISomHDhqlp06b3PM/tduvo\n0aM6c+aMRo4caUFSABQzUEPExsZq3759ys7O1sCBA+9ZPn78eNlsNoWEhGjkyJHq0qWLZ9mbb75Z\nYut6+vTpnt3cACoXxQzUIM2bNy9xYtjd1q9fr1q1apX6uiVLlnCMGfARjjEDAGAQihkAAIOwKxuA\nV3PmzCkx3bdvX40dO9aaMEA1x92lAAAwCLuyAQAwCMUMAIBBKGYAAAxCMQMAYBCKGQAAg1DMAAAY\nhGIGAMAgFDMAAAahmAEAMMj/A6mUHZlI103CAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<matplotlib.figure.Figure at 0x7f589bf54dd8>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "results_like         = %sql -t SELECT COUNT(*) FROM TEMP_LINE WHERE STATION LIKE '%West%'\n",
    "results_string       = %sql -t SELECT COUNT(*) FROM TEMP_LINE WHERE REGEXP_LIKE(STATION,'West')\n",
    "results_capturing    = %sql -t SELECT COUNT(*) FROM TEMP_LINE WHERE REGEXP_LIKE(STATION,'(West)')\n",
    "results_noncapturing = %sql -t SELECT COUNT(*) FROM TEMP_LINE WHERE REGEXP_LIKE(STATION,'(?:West)')\n",
    "%sql -q DROP TABLE RESULTS\n",
    "%sql -q CREATE TABLE RESULTS(TYPE VARCHAR(16), RESULT DEC(9,2))\n",
    "%sql -q INSERT INTO RESULTS VALUES ('LIKE', {results_like} ), \\\n",
    "  ('REGX STRING', {results_string} ), \\\n",
    "  ('REGX CAPTURE', {results_capturing} ), \\\n",
    "  ('REGX NONCAPTURE', {results_noncapturing} )\n",
    "%sql SELECT * FROM RESULTS\n",
    "%sql -pb SELECT * FROM RESULTS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "The index can help speed up processing of the LIKE statement and the regular expression calls. The results may or may not favor LIKE over regular expressions. In summary, if you are going to use regular expressions, you may get a performance advantage by using indexes if possible."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Credits: IBM 2018, George Baklarz [baklarz@ca.ibm.com]"
   ]
  }
 ],
 "metadata": {
  "hide_code_all_hidden": false,
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}