Regex for Data Analysts

Character classes, quantifiers, groups, lookaheads, regex in SQL (BigQuery, PostgreSQL, MySQL), Python re module, pandas str methods, and 12 real-world patterns for cleaning messy data.

-- Indian mobile number: starts with 6/7/8/9, then 9 more digits
Pattern: [6-9]\d{9}
Matches: 9876543210, 8123456789
Rejects: 1234567890 (starts with 1), 98765432 (only 8 digits)

-- With optional country code (+91 or 0):
Pattern: (?:\+91|0)?[6-9]\d{9}
Matches: +919876543210, 09876543210, 9876543210

-- Email address (practical, not RFC-complete):
Pattern: [a-zA-Z0-9._%+\-]+@[a-zA-Z0-9.\-]+\.[a-zA-Z]{2,}
Matches: user@example.com, name.surname+tag@company.co.in
Rejects: @missing.com, noatsign.com

-- Indian PIN code (6 digits, first not 0):
Pattern: [1-9]\d{5}
Matches: 201301, 110001
Rejects: 012345 (starts with 0)

-- Date in DD/MM/YYYY or DD-MM-YYYY:
Pattern: (0?[1-9]|[12]\d|3[01])[\-/](0?[1-9]|1[0-2])[\-/](19|20)\d{2}
Matches: 27/10/2026, 01-01-2025, 5/3/2024

-- Product SKU like "SKU-1234-AB" or "SKU-999-XZ":
Pattern: SKU-\d{3,4}-[A-Z]{2}
Matches: SKU-1234-AB, SKU-999-XZ
Rejects: sku-1234-ab (lowercase), SKU-12-AB (too few digits)

-- REGEXP_LIKE (MySQL 8+) / RLIKE (older MySQL): filter rows
SELECT *
FROM customers
WHERE phone REGEXP '^[6-9][0-9]{9}$';           -- valid Indian mobile

-- Remove all non-digits (MySQL doesn't have REGEXP_REPLACE before 8.0)
-- Use REGEXP_REPLACE (MySQL 8+):
SELECT
    customer_id,
    REGEXP_REPLACE(phone, '[^0-9]', '') AS clean_phone
FROM customers;

-- Extract domain from email
SELECT
    email,
    REGEXP_SUBSTR(email, '@(.+)$', 1, 1, NULL, 1) AS domain
FROM customers;

-- ~ operator: case-sensitive regex match
SELECT * FROM customers WHERE phone ~ '^[6-9][0-9]{9}$';

-- ~* operator: case-insensitive
SELECT * FROM products WHERE description ~* 'premium|pro|enterprise';

-- regexp_replace: remove non-alphanumeric from product codes
SELECT
    product_id,
    regexp_replace(product_code, '[^A-Z0-9]', '', 'g') AS clean_code
FROM products;
-- 'g' flag = global (replace all occurrences, not just first)

-- regexp_matches: extract all matches (returns a set)
SELECT regexp_matches(description, '[A-Z]{2,3}-d{4}', 'g') AS sku
FROM product_descriptions;

-- substring with regex: extract first match
SELECT
    log_entry,
    substring(log_entry FROM 'user_id=(d+)') AS extracted_user_id
FROM event_logs;

-- REGEXP_CONTAINS: boolean filter
SELECT *
FROM orders
WHERE REGEXP_CONTAINS(email, r'[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+.[a-zA-Z]{2,}');
-- Note: BigQuery uses r'...' raw strings (no need to double-escape )

-- REGEXP_EXTRACT: pull first match (returns NULL if no match)
SELECT
    raw_text,
    REGEXP_EXTRACT(raw_text, r'INV-(d{6})') AS invoice_number
FROM transactions;

-- REGEXP_EXTRACT_ALL: all matches as an array
SELECT
    REGEXP_EXTRACT_ALL(description, r'[A-Z]{2,3}-d{4}') AS all_skus
FROM product_catalogue;

-- REGEXP_REPLACE: clean phone numbers
SELECT
    REGEXP_REPLACE(phone, r'[^d]', '') AS digits_only
FROM customers;

-- Lookahead example: extract amount before currency symbol
SELECT
    REGEXP_EXTRACT(price_text, r'(d+(?:.d{2})?)(?=s*(?:INR|₹|Rs))') AS amount
FROM price_table;

import re

text = "Order INV-004521 placed by user 9876543210 on 27/10/2026"

# re.search: find first match anywhere in string
m = re.search(r'INV-(d+)', text)
if m:
    print(m.group(0))   # full match:  'INV-004521'
    print(m.group(1))   # group 1:     '004521'

# re.findall: return all matches as a list
phones = re.findall(r'[6-9]d{9}', text)
print(phones)           # ['9876543210']

# re.sub: replace matches
clean = re.sub(r'[^d]', '', '+91 98765-43210')
print(clean)            # '919876543210'

# re.compile: pre-compile for repeated use (faster in loops)
phone_pattern = re.compile(r'(?:+91|0)?([6-9]d{9})')
for row in df['phone']:
    m = phone_pattern.search(str(row))
    if m:
        print(m.group(1))

# Named groups: cleaner extraction
date_pattern = re.compile(
    r'(?P<day>d{1,2})[/-](?P<month>d{1,2})[/-](?P<year>d{4})'
)
m = date_pattern.search('Order date: 27/10/2026')
if m:
    print(m.group('day'), m.group('month'), m.group('year'))
    # 27  10  2026

import pandas as pd

df = pd.DataFrame({
    'phone':   ['+91 98765-43210', '8123456789', '011-2345-6789', '98765'],
    'email':   ['user@example.com', 'BAD_EMAIL', 'name@co.in', 'test@'],
    'notes':   ['Ref: INV-1234 paid', 'No invoice', 'INV-5678 pending', ''],
})

# str.contains: boolean mask for filtering
valid_phones = df['phone'].str.contains(r'[6-9]d{9}', regex=True, na=False)
print(df[valid_phones])

# str.extract: capture groups → new columns (uses first match)
df[['area', 'number']] = df['phone'].str.extract(
    r'(d{3,5})[-s](d{7,8})'
)

# str.extractall: all matches → multi-index DataFrame
all_invoices = df['notes'].str.extractall(r'(INV-d{4})')
print(all_invoices)

# str.replace: clean and normalise
df['phone_clean'] = df['phone'].str.replace(r'[^d]', '', regex=True)

# str.findall: list of all matches per row
df['invoice_list'] = df['notes'].str.findall(r'INV-d{4}')

# Validate with str.fullmatch (entire string must match)
df['email_valid'] = df['email'].str.fullmatch(
    r'[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+.[a-zA-Z]{2,}'
)

Use regex101.com — paste your pattern, paste sample data, see matches highlighted in real time with an explanation of each part of your pattern. Choose the engine that matches your tool: Python, MySQL, or PCRE for PostgreSQL/BigQuery.