Kenhuang Academy
  • English

      • MySQL Regular Expressions

      Overview

      MySQL provides regular expression support through the REGEXP and RLIKE operators for pattern matching in string data. Regular expressions are powerful tools for complex pattern matching.

      Regular Expression Basics

      • Pattern Matching: Match specific patterns in text
      • Wildcards: Special characters for flexible matching
      • Anchors: Position of match in string
      • Character Classes: Sets of characters to match

      REGEXP Operator

      Basic Syntax

      -- REGEXP (case-sensitive by default)
SELECT 'MySQL' REGEXP 'My';         -- TRUE
SELECT 'mysql' REGEXP 'My';         -- FALSE (case-sensitive)

-- RLIKE is synonym for REGEXP
SELECT 'Hello' RLIKE 'Hell';       -- TRUE

-- NOT REGEXP for negative match
SELECT 'Hello' NOT REGEXP 'World';  -- TRUE

      REGEXP vs LIKE

      -- LIKE uses simple wildcards
SELECT name FROM products WHERE name LIKE '%phone%';
-- Matches: iPhone, smartphone, telephone

-- REGEXP uses powerful patterns
SELECT name FROM products WHERE name REGEXP 'phone|phone';
-- Matches: iPhone, smartphone, telephone, phone

-- Multiple patterns with REGEXP
SELECT name FROM products WHERE name REGEXP 'phone|laptop|tablet';
-- Matches: iPhone, laptop, tablet

-- Equivalent with LIKE would require OR
SELECT name FROM products 
WHERE name LIKE '%phone%' 
   OR name LIKE '%laptop%' 
   OR name LIKE '%tablet%';

      Regular Expression Metacharacters

      Common Metacharacters

      | Pattern |-----------------|------------------|----------------| | ^ | Start of string | $ | End of string | . | Any single character | | | OR operator | * | Zero or more occurrences | + | One or more occurrences | ? | Zero or one occurrence | [ ] | Character class | [^ ] | Negated character class | \ | Escape character

      Examples

      -- Start of string
SELECT 'Hello World' REGEXP '^Hello';      -- TRUE
SELECT 'Hello World' REGEXP '^World';      -- FALSE

-- End of string
SELECT 'Hello World' REGEXP 'World$';      -- TRUE
SELECT 'Hello World' REGEXP 'Hello$';      -- FALSE

-- Any character
SELECT 'cat' REGEXP 'c.t';                -- TRUE
SELECT 'cut' REGEXP 'c.t';                -- TRUE
SELECT 'ct' REGEXP 'c.t';                 -- FALSE (needs one character)

-- OR operator
SELECT 'cat' REGEXP 'cat|dog';            -- TRUE
SELECT 'dog' REGEXP 'cat|dog';            -- TRUE
SELECT 'bird' REGEXP 'cat|dog';           -- FALSE

-- Repetition
SELECT 'ca' REGEXP 'ca*';                -- TRUE (zero a's)
SELECT 'caa' REGEXP 'ca*';               -- TRUE (two a's)
SELECT 'caaa' REGEXP 'ca+';               -- TRUE (one or more a's)
SELECT 'cb' REGEXP 'ca+';                 -- FALSE (no a)

-- Optional
SELECT 'color' REGEXP 'colou?r';          -- TRUE
SELECT 'colour' REGEXP 'colou?r';         -- TRUE

      Character Classes

      Predefined Classes

      | Class |------------|------------------| | [[:alnum:]] | Alphanumeric | [[:alpha:]] | Alphabetic | [[:digit:]] | Digits | [[:lower:]] | Lowercase | [::upper:]] | Uppercase | [[:space:]] | Whitespace | [::punct:]] | Punctuation

      Examples

      -- Alphanumeric
SELECT 'user123' REGEXP '[[:alnum:]]+';     -- TRUE
SELECT 'user_123' REGEXP '[[:alnum:]]+';   -- TRUE (matches part)

-- Digits
SELECT '123' REGEXP '[[:digit:]]+';          -- TRUE
SELECT 'abc' REGEXP '[[:digit:]]+';          -- FALSE

-- Alphabetic
SELECT 'hello' REGEXP '[[:alpha:]]+';         -- TRUE
SELECT 'hello123' REGEXP '[[:alpha:]]+';     -- TRUE (matches hello)

-- Custom character class
SELECT 'cat' REGEXP '[abc]at';              -- TRUE
SELECT 'dog' REGEXP '[abc]at';              -- FALSE

-- Range in character class
SELECT 'cat' REGEXP '[a-z]at';              -- TRUE
SELECT 'dog' REGEXP '[a-z]og';              -- TRUE
SELECT '123' REGEXP '[0-9]+';               -- TRUE

-- Negated character class
SELECT 'cat' REGEXP '[^x]at';              -- TRUE
SELECT 'xat' REGEXP '[^x]at';              -- FALSE

      Practical Examples

      Email Validation

      -- Simple email pattern
SELECT * FROM users 
WHERE email REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$';

-- More specific pattern
SELECT * FROM users 
WHERE email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$';

      Phone Number Matching

      -- Match US phone numbers
SELECT * FROM contacts 
WHERE phone REGEXP '^\\([0-9]{3}\\) [0-9]{3}-[0-9]{4}$';

-- Match various phone formats
SELECT * FROM contacts 
WHERE phone REGEXP '^[0-9]{3}-[0-9]{3}-[0-9]{4}$|^[0-9]{10}$';

      Product Codes

      -- Match product codes like PROD-123
SELECT * FROM products 
WHERE code REGEXP '^[A-Z]{4}-[0-9]{3}$';

-- Match SKU patterns
SELECT * FROM products 
WHERE sku REGEXP '[A-Z]{2}[0-9]{4}[A-Z]';

      Text Search

      -- Find words starting with specific letters
SELECT * FROM articles 
WHERE content REGEXP '\\b[A-M][a-z]+\\b';

-- Find URLs in text
SELECT * FROM messages 
WHERE content REGEXP 'https?://[a-zA-Z0-9./?=&_-]+';

-- Find mentions (@username)
SELECT * FROM tweets 
WHERE content REGEXP '@[a-zA-Z0-9_]+';

      REGEXP_REPLACE Function

      MySQL 8.0+ supports REGEXP_REPLACE for substitution.

      -- Replace digits with X
SELECT REGEXP_REPLACE('Product123', '[0-9]', 'X');
-- Returns: ProductXXX

-- Remove non-alphanumeric characters
SELECT REGEXP_REPLACE('Item #123!', '[^a-zA-Z0-9]', '');
-- Returns: Item123

-- Format phone numbers
SELECT REGEXP_REPLACE('5551234567', '([0-9]{3})([0-9]{3})([0-9]{4})', '(\\1) \\2-\\3');
-- Returns: (555) 123-4567

-- Replace multiple spaces
SELECT REGEXP_REPLACE('Too    many    spaces', '[[:space:]]+', ' ');
-- Returns: Too many spaces

      REGEXP_LIKE / REGEXP_LIKE

      Simple pattern matching without full regex support.

      -- REGEXP_LIKE for simple patterns
SELECT name FROM products WHERE name REGEXP_LIKE 'phone%';
-- Similar to LIKE but uses REGEXP engine

-- Case-insensitive matching
SELECT name FROM products WHERE name REGEXP_LIKE 'phone%';

      Performance Considerations

      REGEXP Performance

      -- REGEXP can be slow on large datasets
-- Consider using indexed columns first

-- Efficient pattern
SELECT * FROM users 
WHERE email REGEXP '^[a-z]+@example\\.com$'
  AND email LIKE '%@example.com%';
-- LIKE uses index if possible, REGEXP refines

-- Avoid REGEXP with leading wildcards
-- Poor: (can't use index)
SELECT * FROM products WHERE name REGEXP '.*phone';

-- Better: (can use index)
SELECT * FROM products WHERE name LIKE 'phone%' OR name LIKE '%phone';

      Alternative Approaches

      -- Use SUBSTRING for fixed patterns
SELECT * FROM products 
WHERE code LIKE 'PROD-%';

-- Use LEFT for prefix matching
SELECT * FROM products 
WHERE LEFT(code, 4) = 'PROD';

-- Use FULLTEXT for text search
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('database' IN NATURAL LANGUAGE MODE);

      Advanced Patterns

      Capturing Groups

      -- Extract parts using REGEXP_SUBSTR (MySQL 8.0+)
SELECT 
    code,
    REGEXP_SUBSTR(code, '[A-Z]+') AS letters,
    REGEXP_SUBSTR(code, '[0-9]+') AS numbers
FROM products
WHERE code = 'PROD123';
-- Returns: letters=PROD, numbers=123

      Word Boundaries

      -- Match whole words
SELECT * FROM articles 
WHERE content REGEXP '\\bdatabase\\b';

-- Match word at start of string
SELECT * FROM articles 
WHERE title REGEXP '^\\bMySQL\\b';

      Repetition Quantifiers

      -- Exact match {n}
SELECT 'aaa' REGEXP 'a{3}';     -- TRUE
SELECT 'aa' REGEXP 'a{3}';      -- FALSE

-- Range match {n,m}
SELECT 'aaa' REGEXP 'a{2,4}';   -- TRUE
SELECT 'aaaaa' REGEXP 'a{2,4}';  -- FALSE (too many)

-- Minimum match {n,}
SELECT 'aaa' REGEXP 'a{2,}';    -- TRUE
SELECT 'a' REGEXP 'a{2,}';     -- FALSE

      REGEXP Case Sensitivity

      -- Case-sensitive (default)
SELECT 'MySQL' REGEXP 'mysql';     -- FALSE

-- Case-insensitive (using BINARY)
SELECT BINARY 'MySQL' REGEXP BINARY 'mysql';  -- FALSE

-- Case-insensitive pattern
SELECT 'MySQL' REGEXP '[Mm][Yy][Ss][Qq][Ll]';  -- TRUE

-- Convert to lowercase
SELECT LOWER('MySQL') REGEXP 'mysql';  -- TRUE

      Common Use Cases

      Data Validation

      -- Validate postal codes
SELECT * FROM addresses 
WHERE postal_code REGEXP '^[0-9]{5}(-[0-9]{4})?$';

-- Validate credit card format
SELECT * FROM payments 
WHERE card_number REGEXP '^[0-9]{13,19}$';

      Data Cleaning

      -- Remove special characters
UPDATE products 
SET name_clean = REGEXP_REPLACE(name, '[^a-zA-Z0-9 ]', '');

-- Standardize phone formats
UPDATE contacts 
SET phone = REGEXP_REPLACE(phone, '[^0-9]', '');

      Pattern Extraction

      -- Extract domain from email
SELECT 
    email,
    REGEXP_SUBSTR(email, '@([a-zA-Z0-9.-]+)\\.[a-z]{2,}$') AS domain
FROM users;

      Summary

      Regular expressions in MySQL provide:

      • Powerful Pattern Matching: Complex patterns beyond LIKE
      • Flexible Matching: Wildcards, character classes, anchors
      • String Functions: REGEXP_REPLACE, REGEXP_SUBSTR (MySQL 8.0+)
      • Search Capabilities: Email, phone, URL validation

      Consider performance and use appropriate indexing when working with large datasets.

      Previous: NULL Values

      Next: Transactions