Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to:
Azure SQL Database
SQL database in Microsoft Fabric
This article introduces regular expressions for SQL Server.
Note
As a preview feature, the technology presented in this article is subject to Supplemental Terms of Use for Microsoft Azure Previews.
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.
This implementation of regular expression is based on the RE2 regular expression library. For more information, visit RE2 Regular Expression Syntax.
For example:
Function | Description |
---|---|
REGEXP_LIKE | Returns a Boolean value that indicates whether the text input matches the regex pattern. |
REGEXP_REPLACE | Returns a modified source string replaced by a replacement string, where occurrence of the regex pattern found. |
REGEXP_SUBSTR | Extracts parts of a string based on a regular expression pattern. Returns Nth occurrence of a substring that matches the regex pattern. |
REGEXP_INSTR | Returns the starting or ending position of the matched substring, depending on the option supplied. |
REGEXP_COUNT | Returns a count of the number of times that regex pattern occurs in a 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.
Kinds of single-character expressions | Examples |
---|---|
any character, possibly including newline (s=true) | . |
character class | [xyz] |
negated character class | [^xyz] |
Perl character classes | \d |
negated Perl character class | \D |
ASCII character classes | [[:alpha:]] |
negated ASCII character class | [[:^alpha:]] |
Unicode character class (one-letter name) | \pN |
Unicode character class | \p{Greek} |
negated Unicode character class (one-letter name) | \PN |
negated Unicode character class | \P{Greek} |
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_] ) |
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] ) |
*
matches zero or more occurrences of the preceding character^
matches the beginning of a lineGroup and capture parts of the pattern with:
( )
[ ]
{ }
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.
Regex queries may 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.
These are the items that are not currently supported in this preview:
string_expressions
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayTraining
Module
Introduction to SQL Server 2022 T-SQL enhancements - Training
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.