description |
Guidelines and best practices for using regular expressions in OceanBase AI Database queries |
|---|---|
| keywords | Regular expression, regexp, rlike, query, string matching |
| dir-name | |
| dir-name-en | |
| tenant-type | MySQL Mode |
Regular expressions are pattern matching tools used to search for and manipulate text that matches a specific pattern within strings. OceanBase AI Database is fully compatible with the regular expression functionality of MySQL 8.0 and supports the extended regular expression syntax of POSIX 1003.2.
OceanBase AI Database uses the ICU regular expression engine, which provides high-performance regular expression matching and manipulation capabilities. It also supports the optional Hyperscan regular expression engine for even better performance.
Regular expression functions and operators
OceanBase AI Database provides the following regular expression-related functions and operators:
Name |
Description |
|---|---|
| REGEXP | Checks whether the string matches the regular expression. |
| REGEXP | Checks whether the string does not match the regular expression. |
| RLIKE | Synonym of REGEXP. |
| REGEXP_LIKE | Matches the string against a regular expression. |
| REGEXP_INSTR | Returns the starting position of the matched substring. |
| REGEXP_REPLACE | Replaces substrings that match a regular expression. |
| REGEXP_SUBSTR | Returns the matched substring. |
Basic syntax
Simple matching
-- Basic matching
SELECT 'hello world' REGEXP 'hello'; -- Returns 1 (match)
SELECT 'hello world' REGEXP 'goodbye'; -- Returns 0 (no match)
-- Using RLIKE
SELECT 'hello world' RLIKE '^hello'; -- Returns 1 (starts with hello)
Character classes and quantifiers
-- A dot matches any character.
SELECT 'abc' REGEXP 'a.c'; -- Returns 1
-- The character class [abc] matches any single character from the set {a, b, c}.
SELECT 'bat' REGEXP '[abc]at'; -- Returns 1
-- Quantifiers: * (zero or more times), + (one or more times), and ? (zero or one time)
SELECT 'caaat' REGEXP 'ca*t'; -- Returns 1 (a occurs zero or more times)
SELECT 'caaat' REGEXP 'ca+t'; -- Returns 1 (a occurs one or more times)
SELECT 'cat' REGEXP 'ca?t'; -- Returns 1 (a occurs zero or one time)
Anchors
-- ^ Matches the beginning of a string
SELECT 'hello world' REGEXP '^hello'; -- Returns 1
-- $ matches the end of a string.
SELECT 'hello world' REGEXP 'world$'; -- Returns 1
-- \b matches a word boundary.
SELECT 'hello world' REGEXP 'world\b'; -- Returns 1
Grouping and referencing
-- Parentheses () are used for grouping.
SELECT 'abab' REGEXP '(ab)+'; -- Returns 1
-- Backreferences \1, \2, etc.
SELECT 'abab' REGEXP '(ab)\1'; -- Returns 1
Character classes
Standard character classes
Character class |
Meaning |
|---|---|
[[:alnum:]] |
Alphanumeric characters |
[[:alpha:]] |
Alphabetical characters |
[[:blank:]] |
White space characters (spaces and tabs) |
[[:cntrl:]] |
Control characters |
[[:digit:]] |
Numeric characters |
[[:graph:]] |
Printable characters (excluding spaces) |
[[:lower:]] |
Lowercase letters |
[[:print:]] |
Printable characters (including spaces) |
[[:punct:]] |
Punctuation |
[[:space:]] |
White space characters (including line breaks and carriage returns) |
[[:upper:]] |
Uppercase letters |
[[:xdigit:]] |
Hexadecimal digits |
-- Using character classes
SELECT 'abc123' REGEXP '[[:alnum:]]+'; -- Returns 1
SELECT 'hello' REGEXP '[[:alpha:]]+'; -- Returns 1
SELECT '123' REGEXP '[[:digit:]]+'; -- Returns 1
Special character classes
-- POSIX character classes
SELECT 'a1' REGEXP '[[:alnum:]]'; -- Returns 1
SELECT 'A' REGEXP '[[:upper:]]'; -- Returns 1
SELECT 'a' REGEXP '[[:lower:]]'; -- Returns 1
-- Equivalence class [=character=]
SELECT 'a' REGEXP '[[=a=]]'; -- Returns 1
-- Collating elements [.character.]
SELECT '.' REGEXP '[[..]]'; -- Returns 1
Escape characters
In regular expressions, some characters have special meanings. To match these characters themselves, you need to use the backslash \ as an escape sequence.
Special character |
Escape sequence |
|---|---|
. |
\. |
* |
\* |
+ |
\+ |
? |
\? |
^ |
\^ |
$ |
\$ |
( |
\( |
) |
\) |
[ |
\[ |
] |
\] |
{ |
\{ |
} |
\} |
| |
\| |
\ |
\\ |
-- Escape examples
SELECT 'a.c' REGEXP 'a\.c'; -- Returns 1 (matches the literal period)
SELECT 'a*c' REGEXP 'a\*c'; -- Returns 1 (matches the literal asterisk)
SELECT 'a+c' REGEXP 'a\+c'; -- Returns 1 (matches the literal plus sign)
SELECT 'price: $100' REGEXP '\\$'; -- Returns 1 (matches the dollar sign)
Character sets and collations
Regular expression operations determine matching behavior and comparison methods based on the character sets and collations of the input string and the regular expression pattern parameters:
- If a parameter has a different character set or collation, the strict rule applies.
- If either parameter is a binary string, it is treated as a case-sensitive binary string.
- Multiple character sets are supported, including UTF-8 and UTF-16.
-- Character set examples
SELECT 'OceanBase' REGEXP 'oceanbase'; -- Returns 0 (case-sensitive)
-- Binary comparison example (character set that requires a binary collation)
SELECT _utf8mb4'OceanBase' COLLATE utf8mb4_bin REGEXP _utf8mb4'oceanbase' COLLATE utf8mb4_bin; -- Returns 0
Notice
The BINARY keyword cannot be used directly in the REGEXP function in OceanBase AI Database. To perform a binary comparison, use the COLLATE clause to specify the character set for the binary collation.
Performance optimization
OceanBase AI Database provides several performance optimizations for regular expressions:
- Expression caching: Compiled regular expressions can be cached and reused.
- Constant optimization: Provides fast computation paths for regular expressions with constant patterns.
- Memory management: Effective temporary memory allocation and management.
Notice
Complex regular expressions may impact query performance. Use them cautiously in high-concurrency scenarios and consider using database indexes to optimize query performance.
Application scenarios
Data extraction
-- Extract digits from a string
SELECT REGEXP_SUBSTR('Price: $123.45', '\\d+\\.\\d+') AS price;
-- Extract domain name from URL
SELECT REGEXP_SUBSTR('https://www.example.com/path', 'https?://([^/]+)') AS domain;
Data cleansing
-- Remove extra whitespace characters
SELECT REGEXP_REPLACE('hello world', '\\s+', ' ') AS cleaned_text;
-- Format a phone number
SELECT REGEXP_REPLACE('12345678901', '(\\d{3})(\\d{4})(\\d{4})', '$1-$2-$3') AS formatted_phone;