Back to Function Index

REGEXP()

Overview

REGEXP() is a powerful text-matching function that can be used in three different ways:

1. Match and return a portion of a string that matches a regular expression
2. Return a sub-portion of a string matching a regular expression, referencing parenthesized groups
3. Return a new string with replacements for parts matching a regular expression

Function Format

REGEXP(string, match string [, string | number])

Return Value

REGEXP() returns a character string

Examples

// Basic pattern matching
REGEXP("2.71828 IS LESS THAN 3.14159", "[0-9.]+") = "2.71828"

// Using capture groups
REGEXP("2.71828 IS LESS THAN 3.14159", "([0-9.]+)([^0-9.]+)([0-9.]+)", 1) = "2.71828"
REGEXP("2.71828 IS LESS THAN 3.14159", "([0-9.]+)([^0-9.]+)([0-9.]+)", 3) = "3.14159"

// Pattern replacement
REGEXP("2.71828 IS LESS THAN 3.14159", "([0-9.]+)([^0-9.]+)([0-9.]+)", "$3 IS CALLED 'PI'") = "3.14159 IS CALLED 'PI'"

Notes

• Important behaviors:
  • Supports standard regular expression syntax
  • Can use capture groups with parentheses
  • Capture groups can be referenced by number
  • Supports replacement patterns with $n notation
  • Returns first match when used for extraction
• Common use cases include:
  • Pattern matching
  • Text extraction
  • String manipulation
  • Data validation
  • Format transformation
• The function supports:
  • Basic pattern matching
  • Capture group extraction
  • Pattern-based replacement
  • Complex text transformations
• Regular expression patterns can include:
  • Character classes
  • Quantifiers
  • Anchors
  • Capture groups
  • Special characters

See Also

• REPLACE()
• SUBSTR()
• TRIM()
• LOWER()