RegexSplit
Splits a string into one or more substrings (Regex Version)
- Free Function
- SQL Server Compatibility: 2008, 2012, 2014, 2016, 2017
SELECT SQLHTTP.net.RegexSplit ( @Text, @RegexPattern, @Index )
| 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. |
| @Index | bigint | Integer value specifying the location of a substring |
nvarchar(MAX)
Split by whitespace sequences of any size:
|
1 2 3 4 5 6 7 8 9 10 11 |
DECLARE @Text nvarchar(MAX) DECLARE @RegexPattern nvarchar(4000) DECLARE @Index bigint SET @Text = 'The quick brown fox jumps over the lazy dog' SET @RegexPattern = '\s+' SET @Index = 4 SELECT SQLHTTP.net.RegexSplit(@Text, @RegexPattern, @Index) |
|
1 2 3 4 |
----- fox |
Split by alphabetic strings to extract numeric values:
|
1 2 3 4 5 6 7 8 9 10 11 |
DECLARE @Text nvarchar(MAX) DECLARE @RegexPattern nvarchar(4000) DECLARE @Index bigint SET @Text = 'Abc1234Def5678Ghi9012Jklm' SET @RegexPattern = '[a-zA-Z]+' SET @Index = 4 SELECT SQLHTTP.net.RegexSplit(@Text, @RegexPattern, @Index) |
|
1 2 3 4 |
----- 9012 |