Regex in spreadsheets, SQL and analytics tools

On the cell phone 9876543210 PIN:560001, the pattern (?<=PIN[ :])[1-9][0-9]{5} returns 560001 in PCRE2, the engine behind Excel’s REGEX functions, and does not compile at all in RE2, the engine behind Google Sheets. Every spreadsheet, database and analytics product calls its regex a pattern, but each has an engine of its own, and the engines disagree about which patterns compile, how a pattern anchors to the value, which syntax reads a word boundary, and whether the operator works out of the box. The right pattern follows from the engine, not from the keyword.

The lesson walks through five families you meet at work: Excel’s REGEX functions, Google Sheets and the other Google products that share its engine, PostgreSQL, MySQL and SQLite. For each it names the engine the vendor documents and shows the pattern you would write; for every engine but MySQL’s ICU, the pattern also runs through that engine’s own library, so the formula comes with real output beside it.

The tool decides the flavour

The vendor pages pin each product to one engine, and the pages are the only source you need before you write a pattern for it. Here is the shape of this lesson’s five families, each row cited to the page that names it:

Three habits follow from the table. First, look up the product’s engine before you reach for a construct, because lookahead, lookbehind, backreferences and possessive quantifiers exist only in some of these engines, and even \d stands for different sets of digits in different ones. Second, when a pattern must live in several of these products at once (a cleaning rule in a Sheets REGEXREPLACE and a nightly PostgreSQL job for the same column, say), write it in the smallest subset the two engines agree on, with the porting method as the checklist. Third, in tools that host regex but choose the engine themselves, read the tool: the MySmartCoPilot regex tester runs the reader’s own browser engine, which is the JavaScript flavour, not RE2 and not PCRE2.

One task, two products: Excel and Sheets

A sheet has an address in each row’s A column and the formula should return the six-digit postal code at the end of it. In Excel the formula is =REGEXEXTRACT(A2, "[1-9][0-9]{5}"); in Google Sheets it reads the same way. Their syntax pages say so: REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity]) for Excel and REGEXEXTRACT(text, regular_expression) for Sheets (an Excel REGEXEXTRACT returns the first match by default, every match for return mode 1 and the groups of the first match for return mode 2; a Sheets REGEXEXTRACT returns the first match, or every capture group when the pattern has them). Both formulas accept the same bare pattern because the pattern uses only the subset the two engines agree on. Here is the Excel side, run through the PCRE2 engine the Microsoft support page names:

# Six-digit PIN code extraction in the PCRE2 flavour, the engine Excel's REGEXEXTRACT also uses (Microsoft support
# page for REGEXTEST). pcre2test reads one pattern per /…/ line and one subject per indented line, so each pattern
# below is tried against the same invented address cells. The last pattern uses a lookbehind, a construct PCRE2
# accepts and the next example will see Go's regexp refuse at compile time.
from pcre2_session import run

run(r"""
# Pattern 1: the shape of a six-digit postal code whose first digit is not 0.
/[1-9][0-9]{5}/
    42 MG Road, Bengaluru 560001
    Flat 7B Palm Court, Chennai 600040
    PO Box 110 085 Delhi

# Pattern 2: substitution. The whole cell becomes "PIN " followed by the first six-digit run; this is the shape of
# the pattern an Excel REGEXREPLACE formula would use for the same job, with the dollar form of a group reference.
/^.*?([1-9][0-9]{5}).*$/g,replace=PIN $1
    Shipment to 42 MG Road, Bengaluru 560001 by 7 PM

# Pattern 3: a lookbehind of (?<=PIN[ :]) keeps a six-digit run only when the four characters before it are "PIN "
# or "PIN:", so a phone number near a PIN label is still ignored. PCRE2 accepts the lookbehind; RE2 refuses it.
/(?<=PIN[ :])[1-9][0-9]{5}/
    PIN 560001 phone 9876543210
    phone 9876543210 PIN:560001
""")

$ pcre2test -q patterns.txt   (PCRE2 10.49)
# Pattern 1: the shape of a six-digit postal code whose first digit is not 0.
/[1-9][0-9]{5}/
    42 MG Road, Bengaluru 560001
 0: 560001
    Flat 7B Palm Court, Chennai 600040
 0: 600040
    PO Box 110 085 Delhi
No match

# Pattern 2: substitution. The whole cell becomes "PIN " followed by the first six-digit run; this is the shape of
# the pattern an Excel REGEXREPLACE formula would use for the same job, with the dollar form of a group reference.
/^.*?([1-9][0-9]{5}).*$/g,replace=PIN $1
    Shipment to 42 MG Road, Bengaluru 560001 by 7 PM
 1: PIN 560001

# Pattern 3: a lookbehind of (?<=PIN[ :]) keeps a six-digit run only when the four characters before it are "PIN "
# or "PIN:", so a phone number near a PIN label is still ignored. PCRE2 accepts the lookbehind; RE2 refuses it.
/(?<=PIN[ :])[1-9][0-9]{5}/
    PIN 560001 phone 9876543210
 0: 560001
    phone 9876543210 PIN:560001
 0: 560001

The first pattern finds the six-digit run in each of the first two cells and no match in the third, where the digits are split by a space. The second pattern is what an Excel REGEXREPLACE formula builds on: the g option replaces every match and $1 in the replacement writes back the captured text. PCRE2 also accepts a lookbehind, (?<=PIN[ :]), which keeps a six-digit run only when the four characters before it spell PIN or PIN:, so a phone number near a PIN label is ignored. That construct is where the two products begin to differ.

The Sheets side runs on Go’s regexp package, whose documentation says it accepts the syntax of RE2, the engine Google Sheets documents. Three things happen in the same five cells:

// The same six-digit postal-code job in Go's regexp, the engine Google Sheets documents for REGEXEXTRACT (its help
// page says Google products use RE2, "except Unicode character class matching"). The first pattern compiles and
// matches the same cells; the second shows what Sheets would send its users for the PIN-near-the-label version,
// which is a different shape of pattern, because Go refuses the lookbehind that worked in PCRE2.
package main

import (
	"fmt"
	"regexp"
)

func main() {
	cells := []string{
		"42 MG Road, Bengaluru 560001",
		"Flat 7B Palm Court, Chennai 600040",
		"PO Box 110 085 Delhi",
		"PIN 560001 phone 9876543210",
		"phone 9876543210 PIN:560001",
	}

	// Shape only, as a Sheets REGEXEXTRACT formula would run on the same cells.
	pin := regexp.MustCompile(`[1-9][0-9]{5}`)
	for _, cell := range cells {
		fmt.Printf("%-40s FindString -> %q\n", cell, pin.FindString(cell))
	}

	// A lookbehind for the PCRE2 case: Go refuses it when the pattern compiles.
	if _, err := regexp.Compile(`(?<=PIN[ :])[1-9][0-9]{5}`); err != nil {
		fmt.Println()
		fmt.Printf("Compile((?<=PIN[ :])…) error: %s\n", err)
	}

	// The RE2 workaround: let the match include the label and keep the group that holds the digits. SubexpIndex
	// returns the number of a named group, so the slice [m[2]:m[3]] holds exactly the PIN.
	labelled := regexp.MustCompile(`PIN[ :](?P<pin>[1-9][0-9]{5})`)
	fmt.Println()
	for _, cell := range cells[3:] {
		m := labelled.FindStringSubmatchIndex(cell)
		if m == nil {
			fmt.Printf("%-40s pin -> no match\n", cell)
			continue
		}
		i := labelled.SubexpIndex("pin")
		fmt.Printf("%-40s pin -> %q\n", cell, cell[m[2*i]:m[2*i+1]])
	}
}

42 MG Road, Bengaluru 560001             FindString -> "560001"
Flat 7B Palm Court, Chennai 600040       FindString -> "600040"
PO Box 110 085 Delhi                     FindString -> ""
PIN 560001 phone 9876543210              FindString -> "560001"
phone 9876543210 PIN:560001              FindString -> "987654"

Compile((?<=PIN[ :])…) error: error parsing regexp: invalid named capture: `(?<=PIN[ :])[1-9][0-9]{5}`

PIN 560001 phone 9876543210              pin -> "560001"
phone 9876543210 PIN:560001              pin -> "560001"

The simple [1-9][0-9]{5} matches the first two addresses and the first “PIN” line. The fifth line (“phone 9876543210 PIN:560001”) shows the trap: a search finds the leftmost six-digit run of the phone number first, 987654, where a reader of the formula expected the PIN. The lookbehind that fixed this in PCRE2 does not compile here: Go reads (?< as the start of a named group and has no lookbehind, so regexp.Compile stops with invalid named capture. The RE2 workaround is to let the match include the label and keep a capture group that holds the digits; SubexpIndex returns the group’s number so the slice gives exactly the PIN.

Case sensitivity also lives in different places. Excel’s REGEXTEST, REGEXEXTRACT and REGEXREPLACE each take a case_sensitivity argument, the third of REGEXTEST, the fourth of REGEXEXTRACT and the fifth of REGEXREPLACE: 0 for case sensitive (the default) and 1 for case insensitive. Sheets has no such argument, so the rule is in the pattern itself: (?i) at the start of an RE2 pattern turns on case-insensitive matching, and (?-i) turns it off again for the rest of the pattern. The engine behind each product decides what the pattern may say.

Analytics filters match the whole value or a part

GA4 and Search Console both run RE2 behind their regex filters, and their defaults are opposites. GA4’s help page says this plainly: “By default, Google Analytics Regex is full regex and case sensitive. Partial regex requires the user to add metacharacters.” Search Console’s page says the other half: its default matching is a “partial match”, and an expression “can match anywhere in the target string unless you use ^ (start of string) or $ (end of string).” Case too differs: GA4 is case sensitive by default; Search Console is case insensitive by default, with (?-i) at the start of the pattern as the switch. The same bare pattern means different things in the two products.

The figure walks one pattern, Bengaluru, through both rules on three invented City values:

The Go run below sends the same values through both rules, so the differences are not promises but outputs:

// GA4's "matches regex" condition is a full-match test by default (its help page: "Google Analytics Regex is full
// regex and case sensitive. Partial regex requires the user to add metacharacters"); Search Console's custom regex
// filter is the other way round (its help page: "Default matching is a 'partial match'. Your regular expression can
// match anywhere in the target string unless you use ^ or $"). The same values go through both rules below, run on
// Go's regexp, which uses the RE2 syntax both products document.
package main

import (
	"fmt"
	"regexp"
)

var cities = []string{
	"Bengaluru",
	"Bangalore",
	"Bengaluru Rural",
	"East Bengaluru",
	"bengaluru",
}

var pages = []string{
	"/learn/regex/",
	"/learn/regex/anchors-and-line-modes/",
	"/learn-regex-free",
	"/not-learn/regex/",
}

func main() {
	// A GA4 filter on City: full match, case sensitive. Only values that are exactly "Bengaluru" pass.
	gaFull := regexp.MustCompile(`^(?:Bengaluru)$`)
	fmt.Println("GA4 City matches regex Bengaluru (full match):")
	for _, c := range cities {
		fmt.Printf("  %-18s %v\n", c, gaFull.MatchString(c))
	}

	// The GA4 partial form the help page describes: wrap the term in ".*".
	gaPartial := regexp.MustCompile(`^(?:.*Bengaluru.*)$`)
	fmt.Println("GA4 City matches regex .*Bengaluru.* (partial match):")
	for _, c := range cities {
		fmt.Printf("  %-18s %v\n", c, gaPartial.MatchString(c))
	}

	// Search Console's custom regex filter: partial by default, case-insensitive by default. The bare term matches
	// anywhere, so Rural and East Bengaluru pass. (?-i) at the start of the pattern turns the case rule off, the
	// help page's own switch; (?i) is Go's spelling of a case-insensitive flag.
	scDefault := regexp.MustCompile(`(?i)Bengaluru`)
	scStrict := regexp.MustCompile(`(?-i)Bengaluru`)
	fmt.Println("Search Console Query matches regex Bengaluru (partial, case-insensitive by default):")
	for _, c := range cities {
		fmt.Printf("  %-18s default -> %-5v  (?-i) -> %v\n", c, scDefault.MatchString(c), scStrict.MatchString(c))
	}

	// BigQuery REGEXP_CONTAINS is a search too, so a page-path pattern meant to count only visits to the regex
	// index also counts every lesson under it. Anchor with ^ and $ to count only the index itself.
	bqContains := regexp.MustCompile(`/learn/regex/`)
	bqAnchored := regexp.MustCompile(`^/learn/regex/$`)
	fmt.Println("BigQuery REGEXP_CONTAINS on page_path:")
	for _, p := range pages {
		fmt.Printf("  %-40s loose -> %-5v  anchored -> %v\n", p, bqContains.MatchString(p), bqAnchored.MatchString(p))
	}
}

GA4 City matches regex Bengaluru (full match):
  Bengaluru          true
  Bangalore          false
  Bengaluru Rural    false
  East Bengaluru     false
  bengaluru          false
GA4 City matches regex .*Bengaluru.* (partial match):
  Bengaluru          true
  Bangalore          false
  Bengaluru Rural    true
  East Bengaluru     true
  bengaluru          false
Search Console Query matches regex Bengaluru (partial, case-insensitive by default):
  Bengaluru          default -> true   (?-i) -> true
  Bangalore          default -> false  (?-i) -> false
  Bengaluru Rural    default -> true   (?-i) -> true
  East Bengaluru     default -> true   (?-i) -> true
  bengaluru          default -> true   (?-i) -> false
BigQuery REGEXP_CONTAINS on page_path:
  /learn/regex/                            loose -> true   anchored -> true
  /learn/regex/anchors-and-line-modes/     loose -> true   anchored -> false
  /learn-regex-free                        loose -> false  anchored -> false
  /not-learn/regex/                        loose -> false  anchored -> false

Three rows deserve a second look. “Bengaluru Rural” is kept by GA4’s partial form (the .* on both sides lets the term sit inside a longer value) and by Search Console’s default (the term matches anywhere); it is dropped by GA4’s default. “bengaluru” with a lowercase b is kept by Search Console’s default (case insensitive) and dropped by GA4 and by Search Console with (?-i) prepended. The BigQuery table shows the matching twin of the same point: REGEXP_CONTAINS is a search, so the pattern /learn/regex/ matches /learn/regex/anchors-and-line-modes/ too; anchoring the pattern with ^ and $ turns it back into an exact filter.

The answer is .*(?:Bengaluru|Mumbai).*, which GA4 reads as if it were ^(?:.*(?:Bengaluru|Mumbai).*)$: the .* on each side lets the name sit inside a longer value, and the non-capturing group keeps both names between the same pair of .*. Search Console, which matches anywhere, needs only Bengaluru|Mumbai, with (?-i) in front when the case must match too.

PostgreSQL brings its own operators and escapes

PostgreSQL calls its engine “advanced regular expressions” (the manual uses that name for Henry Spencer’s package). Four operators do the yes/no work: ~ is case sensitive, ~* case insensitive, and the leading ! negates each. Nine functions handle extraction and replacement, from the long-standing regexp_replace, regexp_match and regexp_matches to the four that PostgreSQL 15 added for compatibility with other databases: regexp_count, regexp_like, regexp_substr and regexp_instr. The run below starts a one-off PostgreSQL 18.6 cluster, loads invented phone numbers in several shapes a form might receive, and asks the questions that come up most:

"""Starts a one-off PostgreSQL 18.6 cluster, runs the regex script on it and prints what psql printed. The server
listens only on 127.0.0.1 on a port the operating system reports as unused and is deleted when the with block ends."""

from pg_session import Server

with Server(settings={"autovacuum": "off", "timezone": "'UTC'"}) as pg:
    pg.up()
    pg.psql("pg_regex.sql")

$ psql -X -a -f pg_regex.sql
-- The PostgreSQL side of the lesson: its own flavour, which the docs call "POSIX Regular Expressions" and whose
-- advanced form (Henry Spencer's package) uses \m and \M for word boundaries, with \b meaning backspace. The
-- script runs on a one-off cluster that pg_session.py starts.
-- A tiny customer table with invented phone numbers in several shapes a product form may receive.
CREATE TABLE customer (name text, phone text);
CREATE TABLE
INSERT INTO customer VALUES
    ('Asha Rao',       '+91 98765 43210'),
    ('Devi Nair',      '09876543210'),
    ('Faisal Shaikh',  '+91-98765-43210'),
    ('Komal Verma',    '98765 43210'),
    ('Pranav Menon',   '91-9876543210 extn 42');
INSERT 0 5
-- The operators ~, ~*, !~ and !~*. ~ is case sensitive, ~* case insensitive, and the ! forms negate the match.
-- !~* is paired with its complement so each row shows which rule picked it up.
SELECT name, phone,
       phone ~  '^\+?91'      AS starts_with_91,
       phone ~* '[a-z]'       AS has_a_letter,
       phone !~ '-'           AS no_hyphen
FROM customer
ORDER BY name;
     name      |         phone         | starts_with_91 | has_a_letter | no_hyphen 
---------------+-----------------------+----------------+--------------+-----------
 Asha Rao      | +91 98765 43210       | t              | f            | t
 Devi Nair     | 09876543210           | f              | f            | t
 Faisal Shaikh | +91-98765-43210       | t              | f            | f
 Komal Verma   | 98765 43210           | f              | f            | t
 Pranav Menon  | 91-9876543210 extn 42 | t              | t            | f
(5 rows)

-- regexp_replace normalises every shape in one pass: drop everything that is not a digit (the 'g' flag applies the
-- replacement to every match), then keep only the last ten digits with regexp_substr. regexp_substr is a 15+
-- function; before that version regexp_matches(…)[1] gives the same answer.
SELECT name,
       phone                                                                 AS raw,
       regexp_replace(phone, '[^0-9]', '', 'g')                              AS digits_only,
       regexp_substr(regexp_replace(phone, '[^0-9]', '', 'g'), '[0-9]{10}$') AS cleaned
FROM customer
ORDER BY name;
     name      |          raw          |  digits_only   |  cleaned   
---------------+-----------------------+----------------+------------
 Asha Rao      | +91 98765 43210       | 919876543210   | 9876543210
 Devi Nair     | 09876543210           | 09876543210    | 9876543210
 Faisal Shaikh | +91-98765-43210       | 919876543210   | 9876543210
 Komal Verma   | 98765 43210           | 9876543210     | 9876543210
 Pranav Menon  | 91-9876543210 extn 42 | 91987654321042 | 7654321042
(5 rows)

-- regexp_matches with the 'g' flag returns one row per match, an array of its capture groups. Here it cuts one
-- number into runs of two digits, the country code first, so a reader can see the function's shape.
SELECT c.name, m[1] AS pair
FROM customer AS c, regexp_matches(c.phone, '([0-9]{2})', 'g') AS m
WHERE c.name = 'Asha Rao'
ORDER BY c.name;
   name   | pair 
----------+------
 Asha Rao | 91
 Asha Rao | 98
 Asha Rao | 76
 Asha Rao | 43
 Asha Rao | 21
(5 rows)

-- regexp_count and regexp_like, two of the four functions PostgreSQL 15 added for compatibility with other databases.
SELECT name,
       regexp_count(phone, '[0-9]')  AS digit_count,
       regexp_like(phone, '^\+?91')  AS country_code_at_start
FROM customer
ORDER BY name;
     name      | digit_count | country_code_at_start 
---------------+-------------+-----------------------
 Asha Rao      |          12 | t
 Devi Nair     |          11 | f
 Faisal Shaikh |          12 | t
 Komal Verma   |          10 | f
 Pranav Menon  |          14 | t
(5 rows)

-- regexp_instr returns the position (one-based) where the first match starts, or zero when none.
SELECT name, phone,
       regexp_instr(phone, '[0-9]{10}') AS first_ten_digit_run_at
FROM customer

Three ideas come out of the output. First, the operators are expressions, so they fit in a SELECT list and in a WHERE clause alike. Second, regexp_replace with the 'g' flag applies the replacement to every match in each value, which is how the second query cleans the numbers: strip everything that is not a digit, then keep the last ten digits with regexp_substr. Its last row shows where such a rule stops: the digits of extn 42 join the number, so the ten digits kept are 7654321042, not the phone number. Third, regexp_matches with 'g' returns one row of matches per occurrence, so a cell with several matches becomes several rows of the result; its sibling without 'g', regexp_match, returns a single array or NULL.

Then comes the trap. PostgreSQL’s regex table 9.20 lists \b as the backspace character (“backspace, as in C”), not as a word boundary; word boundaries are written \m for the start of a word, \M for the end, and \y for either side. The last row of the run shows the three on the same subject:

The advanced regular expressionadvanced regular expression: PostgreSQL's own regex flavour, built on Henry Spencer's package. It adds its own word-boundary escapes \m, \M and \y, treats \b as the backspace character, and ships with the operators ~, ~*, !~ and !~*. family also has flags of its own in the last argument of each regexp function: i for case insensitive, n for newline-sensitive matching, g for every match (on regexp_replace and regexp_matches), x for extended patterns with whitespace and comments. The pattern you can lift straight from Python or PCRE2 is a small one; anything that uses \b, \d in a non-ASCII column or a conditional pattern needs reading before it is pasted here.

MySQL and SQLite: two more engines behind the same keyword

MySQL 8.4 and SQLite share the keyword and differ in almost everything else. MySQL’s reference manual names the engine without ambiguity: “the ICU library used to implement regular expression support”. The four functions are REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR and REGEXP_INSTR, each one with an optional match_type argument of flag letters: c and i for the case rule, m for multiple-line mode, n for a dot that also matches line endings and u for Unix-only line endings. The syntax is ICU’s regular expressions, which cover the Unicode properties and set operations ICU documents on its own site, so \p{Nd} matches any decimal digit of any script, and so does \d, which ICU defines as the same category. A pattern written for PostgreSQL may need rewriting here because MySQL’s \b is the word boundary, not the backspace, and because MySQL has no \m or \M escape at all.

SQLite goes the other way. Its documentation calls the REGEXP operator “a special syntax for the regexp() user function” and says that no such function is defined by default, so a query that uses the operator normally stops with an error. A program that adds its own function named regexp at run time makes the operator work. The sqlite3 command-line shell is the exception you may meet first: its documentation says it is built with extensions the library lacks, among them POSIX extended regular expressions bound to REGEXP, so a query that works in the shell can still fail in a program that opens the same file through the library.

The argument order matters: X REGEXP Y is a call to regexp(Y, X), so the pattern is the first argument. Python’s sqlite3 module lets a short wrapper fill the gap in two lines. The run shows the error first, so you have seen it:

"""SQLite's REGEXP operator stands for a user function the program using the library adds; the library ships
without one, so `WHERE name REGEXP ?` is an error until the program registers it. Python's sqlite3 is one of the
programs that lets a short re-based function fill the gap in a few lines (its documentation calls this
`create_function`). The run uses the SQLite built into Python 3.14.8; the lesson text names the version."""

import re
import sqlite3


def show(label, cur):
    print(label)
    for row in cur.fetchall():
        print(" ", row)
    print()


conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE product(code text, kind text)")
conn.executemany("INSERT INTO product(code, kind) VALUES (?, ?)", [
    ("P-00042-A", "pens"),
    ("P-00017-B", "pens"),
    ("P-00099-7", "paper"),
    ("P-100042",  "pens"),
    ("P-00018-C", "notebooks"),
])

# Step 1: use REGEXP without defining it. The library replies with a plain error that names the missing function.
try:
    conn.execute("SELECT code FROM product WHERE code REGEXP '^P-[0-9]{5}-[A-Z]$'").fetchall()
except sqlite3.OperationalError as e:
    print("Step 1 (no regexp function):", e)
    print()

# Step 2: give SQLite a regexp function. The docs say REGEXP calls regexp(pattern, text) in that order; this
# function returns 1 for a match and 0 for no match (SQLite has no boolean type of its own, so an INTEGER answers
# a WHERE clause the same way).
def regexp(pattern, text):
    return 1 if text is not None and re.search(pattern, text) else 0


conn.create_function("regexp", 2, regexp, deterministic=True)

# Step 3: the same query now works, and the pattern is a Python re pattern.
show("Step 3 (REGEXP works):",
     conn.execute("SELECT code, kind FROM product "
                  "WHERE code REGEXP '^P-[0-9]{5}-[A-Z]$' ORDER BY code"))

# Step 4: the pattern is a Python re pattern, so the host language's quirks follow the pattern into SQL.
# Python's re \d matches Devanagari digits as well as ASCII, so a pattern meant for an ASCII code catches a value
# a reader did not expect. Use [0-9] when the format is ASCII-only.
conn.execute("CREATE TABLE digits(v text)")
conn.executemany("INSERT INTO digits(v) VALUES (?)", [("42",), ("\u096a\u0968",), ("A2",)])
show("Step 4 (\\d is Python's \\d):",
     conn.execute("SELECT v, (v REGEXP '^\\d+$') AS d, (v REGEXP '^[0-9]+$') AS ascii_only FROM digits"))

conn.close()

Step 1 (no regexp function): no such function: REGEXP

Step 3 (REGEXP works):
  ('P-00017-B', 'pens')
  ('P-00018-C', 'notebooks')
  ('P-00042-A', 'pens')

Step 4 (\d is Python's \d):
  ('42', 1, 1)
  ('४२', 1, 0)
  ('A2', 0, 0)

Step 1 prints the exact error a program meets without a function, no such function: REGEXP. Step 2 registers a Python function that takes a pattern and a text and returns 1 or 0; the function in this run uses re.search for a partial match, which is how most ad-hoc regexp() functions are built. Step 3 runs the same query the error line tried, and it works. Step 4 is a reminder of what comes along for the ride: once a Python function is behind REGEXP, the host language’s quirks follow the pattern into SQL. Python’s re \d matches Devanagari digits as well as ASCII digits, so a shape check meant for ASCII codes keeps ४२ too. Use [0-9] when the format is ASCII-only.

Which user-defined functionuser-defined function: A function the host program adds to the database at run time, so a SQL operator or expression can call it. SQLite leaves REGEXP to such a function; without one the operator is an error. a program adds is up to that program: a Node.js service whose regexp() wraps new RegExp(pattern).test(text) gets JavaScript’s flavour, and a Go service whose function wraps Go’s regexp package gets RE2 syntax. The flavour you can rely on is the one the program has set, not one SQLite picks for you.

Which syntax where, in one rule of thumb

• Excel: PCRE2. Everything that works in a PCRE2 tester works here, lookaround and backreferences included; the case_sensitivity argument of each REGEX function is 0 for case sensitive (default) or 1 for case insensitive.
• Google Sheets, BigQuery, GA4, Search Console: RE2. Keep patterns to the shape Go’s regexp compiles, add (?i) to a pattern when a product has no case flag of its own, and remember GA4 is a full-match filter by default while Search Console is partial.
• PostgreSQL: its own advanced regular expressions. \m, \M, \y for word boundaries, \b is backspace, and the regexp_count, regexp_like, regexp_substr and regexp_instr functions are there from version 15.
• MySQL 8.4: ICU. Unicode property escapes work; a match_type argument of flag letters replaces the inline (?i) other engines use.
• SQLite: whatever the host adds. Expect an error until a regexp() function is registered, and know which flavour that function uses.

The cleaning-text-data-with-regex lesson picks up where this one leaves off for the pattern side of a normalisation job in Python and JavaScript; the SQL side’s LIKE and its own pattern operators are covered in LIKE and pattern matching.

Practise: write five filters that both engines accept

The exercise asks for five regex filters that fit the lesson’s constraints: each pattern uses only the subset RE2 accepts (no lookaround, no backreferences, no possessive quantifiers), each one is applied with full-match semantics as GA4 does, and a sixth pattern cleans a value in place as regexp_replace(…, 'g') would. The sample tests check the filter’s behaviour on invented values and refuse any pattern that uses a construct RE2 refuses.

// filters.mjs: five RE2-safe pattern strings and one cleaning function. Keep each pattern in PATTERNS as a bare
// string (no anchors, no slashes): the runner wraps every pattern as ^(?:...)$ for a full match, the way GA4
// does. The tests check what each pattern keeps and refuse any construct Go's regexp would not compile.

export const PATTERNS = {
  // Starter: these drafts pass some tests and fail others. Three of them use constructs Go refuses.
  COUNTRY: 'india',
  LEARN_PATH: '(?<=/learn/).+',
  LEVEL: 'WARN|ERROR|FATAL',
  PRODUCT_CODE: 'PRD-[0-9]{5,6}',
  BENGALURU_AREA: 'Bengaluru(.*)\\1',
};

/**
 * Replace every run of non-alphanumeric characters with a single hyphen, and strip a leading or trailing hyphen.
 * Return "" for "" and for a value made only of non-alphanumeric characters.
 */
export function cleanLabel(value) {
  // Starter: a draft that keeps too much. Rewrite with one regex substitution and a trim.
  return value.toString();
}

import { test, assert } from 'mysmartcopilot:test';
import { PATTERNS, cleanLabel } from './filters.mjs';

// Apply every pattern as a full match, the way GA4's "matches regex" condition does.
function keeps(name, value) {
  return new RegExp('^(?:' + PATTERNS[name] + ')$').test(value);
}

// The constructs Go's regexp would not compile, found in the source of a pattern string.
function notInGo(source) {
  const found = [];
  const kept = source.replace(/\\\\/g, ''); // an escaped backslash starts nothing
  const plain = kept.replace(/\\./g, '_'); // an escaped character is a literal
  if (/\\[1-9]|\\k</.test(kept) || /\(\?P=/.test(plain)) found.push('a backreference');
  if (/\(\?<?[=!]/.test(plain)) found.push('a lookaround');
  if (/\(\?>/.test(plain)) found.push('an atomic group');
  if (/[*+?}]\+/.test(plain)) found.push('a possessive quantifier');
  return found;
}

test('COUNTRY keeps India and Bharat, rejects every other spelling', () => {
  for (const v of ['India', 'Bharat']) assert.ok(keeps('COUNTRY', v), v + ' should pass');
  for (const v of ['india', 'INDIA', 'Bharath', 'Indias', 'South India']) {
    assert.ok(!keeps('COUNTRY', v), v + ' should not pass');
  }
});

test('LEARN_PATH keeps paths under /learn/, rejects the folder itself and other prefixes', () => {
  for (const v of ['/learn/regex/', '/learn/sql/joins/', '/learn/a']) {
    assert.ok(keeps('LEARN_PATH', v), v + ' should pass');
  }
  for (const v of ['/learn/', '/learn', '/learning/regex/', '/docs/learn/']) {
    assert.ok(!keeps('LEARN_PATH', v), v + ' should not pass');
  }
});

test('LEVEL keeps WARN, ERROR and FATAL only', () => {
  for (const v of ['WARN', 'ERROR', 'FATAL']) assert.ok(keeps('LEVEL', v), v + ' should pass');
  for (const v of ['INFO', 'DEBUG', 'TRACE', 'warn', 'ERRORS']) {
    assert.ok(!keeps('LEVEL', v), v + ' should not pass');
  }
});

test('PRODUCT_CODE keeps PRD- then 5 or 6 digits', () => {
  for (const v of ['PRD-00042', 'PRD-100042', 'PRD-99999']) {
    assert.ok(keeps('PRODUCT_CODE', v), v + ' should pass');
  }
  for (const v of ['PRD-4242', 'PRD-1234567', 'prd-00042', 'PRD-00042-A', 'XPRD-00042']) {
    assert.ok(!keeps('PRODUCT_CODE', v), v + ' should not pass');
  }
});

test('BENGALURU_AREA keeps any value that holds Bengaluru anywhere', () => {
  for (const v of ['Bengaluru', 'Bengaluru Rural', 'East Bengaluru', 'South Bengaluru Zone']) {
    assert.ok(keeps('BENGALURU_AREA', v), v + ' should pass');
  }
  for (const v of ['Bangalore', 'Bellur', 'bengaluru', 'Lu-Lu-Luru']) {
    assert.ok(!keeps('BENGALURU_AREA', v), v + ' should not pass');
  }
});

test('every pattern in PATTERNS is one Go would compile', () => {
  for (const name of ['COUNTRY', 'LEARN_PATH', 'LEVEL', 'PRODUCT_CODE', 'BENGALURU_AREA']) {
    assert.ok(name in PATTERNS, 'PATTERNS.' + name + ' should hold the pattern of that filter');
    const source = PATTERNS[name];
    assert.ok(typeof source === 'string' && source.length > 0, 'PATTERNS.' + name + ' should be a non-empty string');
    assert.deepEqual(notInGo(source), [], 'PATTERNS.' + name + ' is "' + source + '"');
  }
});

test('cleanLabel: one regex substitution with a global flag, then a trim', () => {
  assert.equal(cleanLabel('India - Online!'), 'India-Online');
  assert.equal(cleanLabel('  spaces   around  '), 'spaces-around');
  assert.equal(cleanLabel('A/B/C'), 'A-B-C');
  assert.equal(cleanLabel('42abc'), '42abc');
  assert.equal(cleanLabel('---'), '');
  assert.equal(cleanLabel(''), '');
  assert.equal(cleanLabel('€€€Growth™'), 'Growth');
});

"""filters.py: five RE2-safe pattern strings and one cleaning function. Keep each pattern in PATTERNS as a bare
string (no anchors): the runner wraps every pattern as ^(?:...)$ for a full match, the way GA4 does. The tests
check what each pattern keeps and refuse any construct Go's regexp would not compile."""

PATTERNS = {
    # Starter: these drafts pass some tests and fail others. Three of them use constructs Go refuses.
    "COUNTRY": "india",
    "LEARN_PATH": "(?<=/learn/).+",
    "LEVEL": "WARN|ERROR|FATAL",
    "PRODUCT_CODE": "PRD-[0-9]{5,6}",
    "BENGALURU_AREA": "Bengaluru(.*)\\1",
}


def clean_label(value):
    """Replace every run of non-alphanumeric characters with a single hyphen, and strip leading and trailing
    hyphens. Return '' for '' and for a value of only non-alphanumeric characters."""
    # Starter: a draft that keeps too much. Rewrite with one regex substitution and a strip.
    return str(value)

import re

from filters import PATTERNS, clean_label


def keeps(name, value):
    """Apply the named pattern as a full match, the way GA4's 'matches regex' condition does."""
    return re.fullmatch("(?:" + PATTERNS[name] + ")", value) is not None


def not_in_go(source):
    """The constructs Go's regexp would not compile, found in the source of a pattern string."""
    found = []
    kept = source.replace("\\\\", "")  # an escaped backslash starts nothing
    plain = re.sub(r"\\.", "_", kept)  # an escaped character is a literal
    if re.search(r"\\[1-9]|\\k<", kept) or "(?P=" in plain:
        found.append("a backreference")
    if re.search(r"\(\?<?[=!]", plain):
        found.append("a lookaround")
    if "(?>" in plain:
        found.append("an atomic group")
    if re.search(r"[*+?}]\+", plain):
        found.append("a possessive quantifier")
    return found


def test_country_keeps_india_and_bharat_only():
    """COUNTRY keeps India and Bharat, rejects every other spelling"""
    for value in ["India", "Bharat"]:
        assert keeps("COUNTRY", value), f"{value} should pass"
    for value in ["india", "INDIA", "Bharath", "Indias", "South India"]:
        assert not keeps("COUNTRY", value), f"{value} should not pass"


def test_learn_path_keeps_paths_under_learn():
    """LEARN_PATH keeps paths under /learn/, rejects the folder itself and other prefixes"""
    for value in ["/learn/regex/", "/learn/sql/joins/", "/learn/a"]:
        assert keeps("LEARN_PATH", value), f"{value} should pass"
    for value in ["/learn/", "/learn", "/learning/regex/", "/docs/learn/"]:
        assert not keeps("LEARN_PATH", value), f"{value} should not pass"


def test_level_keeps_warn_error_fatal_only():
    """LEVEL keeps WARN, ERROR and FATAL only"""
    for value in ["WARN", "ERROR", "FATAL"]:
        assert keeps("LEVEL", value), f"{value} should pass"
    for value in ["INFO", "DEBUG", "TRACE", "warn", "ERRORS"]:
        assert not keeps("LEVEL", value), f"{value} should not pass"


def test_product_code_keeps_prd_then_five_or_six_digits():
    """PRODUCT_CODE keeps PRD- then 5 or 6 digits"""
    for value in ["PRD-00042", "PRD-100042", "PRD-99999"]:
        assert keeps("PRODUCT_CODE", value), f"{value} should pass"
    for value in ["PRD-4242", "PRD-1234567", "prd-00042", "PRD-00042-A", "XPRD-00042"]:
        assert not keeps("PRODUCT_CODE", value), f"{value} should not pass"


def test_bengaluru_area_keeps_any_value_with_bengaluru_in_it():
    """BENGALURU_AREA keeps any value that holds Bengaluru anywhere"""
    for value in ["Bengaluru", "Bengaluru Rural", "East Bengaluru", "South Bengaluru Zone"]:
        assert keeps("BENGALURU_AREA", value), f"{value} should pass"
    for value in ["Bangalore", "Bellur", "bengaluru", "Lu-Lu-Luru"]:
        assert not keeps("BENGALURU_AREA", value), f"{value} should not pass"


def test_every_pattern_compiles_in_go():
    """every pattern in PATTERNS is one Go would compile"""
    for name in ["COUNTRY", "LEARN_PATH", "LEVEL", "PRODUCT_CODE", "BENGALURU_AREA"]:
        assert name in PATTERNS, f"PATTERNS[{name!r}] should hold the pattern of that filter"
        source = PATTERNS[name]
        assert isinstance(source, str) and source, f"PATTERNS[{name!r}] should be a non-empty string"
        assert not_in_go(source) == [], f"PATTERNS[{name!r}] is {source!r}"


def test_clean_label_substitutes_runs_of_other_characters_and_trims():
    """clean_label: one regex substitution with a global flag, then a strip"""
    assert clean_label("India - Online!") == "India-Online"
    assert clean_label("  spaces   around  ") == "spaces-around"
    assert clean_label("A/B/C") == "A-B-C"
    assert clean_label("42abc") == "42abc"
    assert clean_label("---") == ""
    assert clean_label("") == ""
    assert clean_label("***Growth***") == "Growth"

Then test yourself on the five engines. The questions start with the idea the lesson has shown least so far, and each answer you pick comes with the reason it holds or the detail it leaves out:

Interview questions

Warm-up: why can one regex pattern work in Excel’s REGEXEXTRACT and fail in Google Sheets’ REGEXEXTRACT?

Because the two formulas share a name and little else. The Microsoft support page for REGEXTEST says Excel’s REGEX functions use the PCRE2 flavour; the Google Sheets REGEXMATCH page says Google products use RE2, and that Sheets supports RE2 “except Unicode character class matching”. The two overlap in the easy patterns, so most short formulas move across unchanged. They part on what RE2 leaves out to match in time linear in the text: lookahead, lookbehind, backreferences, possessive quantifiers and atomic groups. A pattern with any of those compiles in Excel and makes the Sheets formula fail. Case differs too: Excel’s REGEX functions take a case_sensitivity argument that switches it, while Sheets has none, so a Sheets pattern starts with (?i). The safe move is to write the pattern in the subset both engines accept and test it with the engine the product documents before saving the formula.

Two situations take these rules further: moving a GA4 segment definition into SQL over exported event data, and cleaning a large PostgreSQL table’s phone numbers with regexp_replace in place. Plan your own opening move for each one before you open its first step below.

Key takeaways

• The product’s keyword is only the handle; the engine behind it (PCRE2 for Excel, RE2 for Google’s data products, PostgreSQL’s advanced regular expressions, ICU for MySQL, a host function for SQLite) decides which patterns compile.
• Spreadsheet REGEX functions take a pattern argument in both Excel and Google Sheets, but Excel’s case_sensitivity argument has no counterpart in Sheets, and Sheets refuses the lookaround and backreferences Excel accepts.
• GA4 “matches regex” is a full-match test by default, Search Console’s custom regex filter is a partial match by default, and BigQuery REGEXP_CONTAINS is a search; .* and ^…$ turn each one into the other.
• PostgreSQL uses \m, \M and \y for word boundaries because \b means backspace, and its regexp_count, regexp_like, regexp_substr and regexp_instr arrived in version 15.
• MySQL 8.4’s REGEXP functions use ICU, so Unicode property escapes work; SQLite’s REGEXP operator is an error until the host program registers a regexp() function, and the flavour of that function is the host’s choice.