RegexSplits
Splits a string into one or more substrings and returns a table (Regex Version)
- Free Function
- SQL Server Compatibility: 2008, 2012, 2014, 2016, 2017
SELECT *
FROM SQLHTTP.net.RegexSplits ( @Text, @RegexPattern )
| Name | Type | Description |
|---|---|---|
| @Text | nvarchar(MAX) |
String expression containing substrings and delimiters. |
| @RegexPattern | nvarchar(4000) | String expression containing a regex expression. See Regular Expression Language – Quick Reference for more information. Even though this parameter is required, a NULL value indicates that no regex expression is to be used. |
| Column name | Data type | Description |
|---|---|---|
| RowID | int | Item Index |
| Text | nvarchar(MAX) | Item Content |
Split by whitespace sequences of any size:
|
1 2 3 4 5 6 7 8 9 10 |
DECLARE @Text nvarchar(MAX) DECLARE @RegexPattern nvarchar(4000) SET @Text = 'The quick brown fox jumps over the lazy dog' SET @RegexPattern = '\s+' SELECT * FROM SQLHTTP.net.RegexSplits(@Text, @RegexPattern) |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
RowID Text ----------- ----------- 1 The 2 quick 3 brown 4 fox 5 jumps 6 over 7 the 8 lazy 9 dog |
Split by alphabetic strings to extract numeric values:
|
1 2 3 4 5 6 7 8 9 10 |
DECLARE @Text nvarchar(MAX) DECLARE @RegexPattern nvarchar(4000) SET @Text = 'Abc1234Def5678Ghi9012Jklm' SET @RegexPattern = '[a-zA-Z]+' SELECT * FROM SQLHTTP.net.RegexSplits(@Text, @RegexPattern) |
|
1 2 3 4 5 6 7 8 9 |
RowID Text ----------- ------------ 1 2 1234 3 5678 4 9012 5 |