A regular expression, or regex, is a sequence of characters that defines a search pattern for text. Regex is commonly used for a variety of tasks including pattern matching, data validation, data transformation, and querying. It offers a flexible and an efficient way to search, manipulate, and handle complex data operations.
Note
Regular expressions are available in Azure SQL Managed Instance configured with the Always-up-to-date update policy.
Returns a table of strings split, delimited by the regex pattern. If there's no match to the pattern, the function returns the string.
Regular expressions can be composed of literal characters and meta-characters, which have special meanings and functions.
A simple regular expression is a single literal character. Characters match themselves, except for the meta-characters. Meta-characters include *, +, ?, (, ), or |. To match a meta-character, escape it with a backslash. For example, \* matches the literal asterisk (*) character.
Accepted regular expression characters
Single character expressions
Kinds of single-character expressions
Examples
any character, possibly including newline (s=true)
The following table lists currently supported Perl character classes.
Perl character classes (all ASCII-only)
Description
\d
digits ( [0-9])
\D
not digits ( [^0-9])
\s
whitespace ( [\t\n\f\r ])
\S
not whitespace ( [^\t\n\f\r ])
\w
word characters ( [0-9A-Za-z_])
\W
not word characters ( [^0-9A-Za-z_])
ASCII character classes
The following table lists currently supported ASCII character classes.
ASCII character classes
Description
[[:alnum:]]
alphanumeric ( [0-9A-Za-z])
[[:alpha:]]
alphabetic ( [A-Za-z])
[[:ascii:]]
ASCII ( [\x00-\x7F])
[[:blank:]]
blank ( [\t ])
[[:cntrl:]]
control ( [\x00-\x1F\x7F])
[[:digit:]]
digits ( [0-9])
[[:graph:]]
graphical ( [!-~] or [A-Za-z0-9!"#$%&'()*+,\-./:;<=>?@[\\\]^_`{|}~])
[[:lower:]]
lower case ( [a-z])
[[:print:]]
printable ( [ -~] or [ [:graph:]])
[[:punct:]]
punctuation ( [!-/:-@[-\``{-~])
[[:space:]]
whitespace ( [\t\n\v\f\r ])
[[:upper:]]
upper case ( [A-Z])
[[:word:]]
word characters ( [0-9A-Za-z_])
[[:xdigit:]]
hex digit ( [0-9A-Fa-f])
Literal characters
Letters
Numbers
Symbols
Metacharacters
* matches zero or more occurrences of the preceding character
^ matches the beginning of a line
Groupings
Group and capture parts of the pattern with:
Parentheses ( )
Brackets [ ]
Braces { }
Flags
Use flags to modify the expression behavior. For example:
i
m
s
c
This implementation supports the POSIX standard of regular expressions following RE2, and has support for the PCRE/PCRE2 flavor of regular expressions syntax, which is compatible with most modern regular expression engines and tools. There are different flavors of regular expressions, such as POSIX, ANSI, Perl, and PCRE, which have different syntax and features.
Requirements
A SQL client tool, such as Azure Data Studio, SQL Server Management Studio, or Visual Studio Code.
A basic knowledge of SQL syntax and queries.
A basic understanding of regular expression syntax and concepts.
FAQ
What is the performance impact of using regex?
Regex queries can have a performance impact depending on the complexity of the regex pattern, the size of the text data, and the number of rows involved. You can use the execution plan and statistics to monitor and optimize the performance of your regex queries.
Known Issues, behaviors, and limitations
These are the items that aren't currently supported in this preview:
LOB data types (varchar(max) or nvarchar(max)) for string_expressions
Regular expression functions aren't supported on memory-optimized OLTP tables
SQL Server 2022 introduces a range of new features and enhancements, providing powerful tools and capabilities that optimize performance and offer better control on SQL objects manipulation and creation.