REGEXP_SPLIT_TO_TABLE preview

2025-05-22

Applies to: SQL Server 2025 (17.x) Preview Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric

Note

As a preview feature, the technology presented in this article is subject to Supplemental Terms of Use for Microsoft Azure Previews.

Returns a table of strings split, delimited by the regex pattern. If there's no match to the pattern, the function returns the string.

REGEXP_SPLIT_TO_TABLE
     (
      string_expression,
      pattern_expression [, flags ]
     )

Requires database compatibility level 170. To set database compatibility level, review ALTER DATABASE (Transact-SQL) compatibility level.

Arguments

string_expression

An expression of a character string.

Can be a constant, variable, or column of character string.

Data types: char, nchar, varchar, or nvarchar.

Note

The REGEXP_LIKE, REGEXP_COUNT, and REGEXP_INSTR functions support LOB types (varchar(max) and nvarchar(max)) up to 2 MB for the string_expression parameter.

pattern_expression

Regular expression pattern to match. Usually a text literal.

Data types: char, nchar, varchar, or nvarchar. pattern_expression supports a maximum character length of 8,000 bytes.

flags

One or more characters that specify the modifiers used for searching for matches. Type is varchar or char, with a maximum of 30 characters.

For example, ims. The default is c. If an empty string (' ') is provided, it will be treated as the default value ('c'). Supply c or any other character expressions. If flag contains multiple contradictory characters, then SQL Server uses the last character.

For example, if you specify ic the regex returns case-sensitive matching.

If the value contains a character other than those listed at Supported flag values, the query returns an error like the following example:

Invalid flag provided. '<invalid character>' are not valid flags. Only {c,i,s,m} flags are valid.

Supported flag values

Flag	Description
`i`	Case-insensitive (default `false`)
`m`	Multi-line mode: `^` and `$` match begin/end line in addition to begin/end text (default `false`)
`s`	Let `.` match `\n` (default `false`)
`c`	Case-sensitive (default `true`)

Returns

REGEXP_SPLIT_TO_TABLE returns the following two-column table:

Column name	Data type	Description
`value`	Same type as `string_expression` or varchar	If the delimiter is found, it's the matching substring. Otherwise it's the whole expression.
`ordinal`	bigint	1-based index value of each substring position from the input expression.

Return a table split for the quick brown fox jumps over the lazy dog.

SELECT *
FROM REGEXP_SPLIT_TO_TABLE('the quick brown fox jumps over the lazy dog', '\s+');

Value  Ordinal
the    1
quick  2
brown  3
fox    4
jumps  5
over   6
the    7
lazy   8
dog    9

Share via