AdvancedSplits
Splits a string into one or more substrings and returns a table (Advanced Version)
- Free Function
- SQL Server Compatibility: 2008, 2012, 2014, 2016, 2017
SELECT *
FROM SQLHTTP.net.AdvancedSplits ( @Text, @Delimiter, @IsDelimiterCharArray, @RegexPattern )
| Name | Type | Description |
|---|---|---|
| @Text | nvarchar(MAX) |
String expression containing substrings and delimiters. |
| @Delimiter | nvarchar(4000) |
String of characters used to identify substring limits. |
| @IsDelimiterCharArray | bit |
Boolean expression describing the composition of delimiter composed of more than one character. |
| @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 |
| StartPosition | int | Start Position |
Split by a space character and a simple regex pattern:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
DECLARE @Text nvarchar(MAX) DECLARE @Delimiter nvarchar(4000) DECLARE @IsDelimiterCharArray bit DECLARE @RegexPattern nvarchar(4000) SET @Text = 'The quick brown fox jumps over the lazy dog' SET @Delimiter = ' ' --one space character SET @IsDelimiterCharArray = 0 --not used in this example SET @RegexPattern = 'o' --This RegexPattern limits the results to words containing the letter 'o' SELECT * FROM SQLHTTP.net.AdvancedSplits(@Text, @Delimiter, @IsDelimiterCharArray, @RegexPattern) |
|
1 2 3 4 5 6 7 8 |
RowID Text StartPosition -------- ----------- ------------- 1 brown 11 2 fox 17 3 over 27 4 dog 41 |
Split by a Char Array delimiter:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
DECLARE @Text nvarchar(MAX) DECLARE @Delimiter nvarchar(4000) DECLARE @IsDelimiterCharArray bit DECLARE @RegexPattern nvarchar(4000) SET @Text = 'The quick brown fox jumps over the lazy dog' SET @Delimiter = 'f ' --two separate single character delimiters... SET @IsDelimiterCharArray = 1 --...since this was set to 1 (true) SET @RegexPattern = NULL --not used in this example SELECT * FROM SQLHTTP.net.AdvancedSplits(@Text, @Delimiter, @IsDelimiterCharArray, @RegexPattern) |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
RowID Text StartPosition -------- ------------ ------------- 1 The 1 2 quick 5 3 brown 11 4 17 5 ox 18 6 jumps 21 7 over 27 8 the 32 9 lazy 36 10 dog 41 |