Skip to content

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

Latest commit

 

History

History
82 lines (61 loc) · 2.37 KB

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

File metadata and controls

82 lines (61 loc) · 2.37 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 05/19/2025
ms.service sql
ms.subservice t-sql
ms.topic reference
dev_langs
TSQL
monikerRange = sql-server-ver17 || = sql-server-linux-ver17 || =azuresqldb-current || =azuresqldb-mi-current || =fabric
ms.custom
build-2025

REGEXP_SPLIT_TO_TABLE preview

[!INCLUDE sqlserver2025-asdb-asmi-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