Skip to content

regexp-split-to-table-transact-sql.md

Latest commit

 

History

History
81 lines (59 loc) · 2.3 KB

regexp-split-to-table-transact-sql.md

File metadata and controls

81 lines (59 loc) · 2.3 KB
title REGEXP_SPLIT_TO_TABLE (Transact-SQL)
description Returns a table of strings split, delimited by the regex pattern. If there's no match to the pattern, the function returns the string.
author MikeRayMSFT
ms.author mikeray
ms.reviewer abhtiwar, wiassaf, randolphwest
ms.date 12/31/2024
ms.service sql
ms.subservice t-sql
ms.topic reference
dev_langs
TSQL
monikerRange = sql-server-ver17 || = sql-server-linux-ver17 || =azuresqldb-current || =fabric

REGEXP_SPLIT_TO_TABLE

[!INCLUDE sqlserver2025-asdb-fabric]

[!INCLUDE preview]

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

[!INCLUDE regexp-string-expression]

pattern_expression

[!INCLUDE regexp-pattern-expression]

flags

[!INCLUDE regexp-flags-expression]

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

Related content