Splits
Splits a string into one or more substrings and returns a table (Simple Version)
- Free Function
- SQL Server Compatibility: 2008, 2012, 2014, 2016, 2017
SELECT *
FROM SQLHTTP.net.Splits ( @Text, @Delimiter )
| Name | Type | Description |
|---|---|---|
| @Text | nvarchar(MAX) | String expression containing substrings and delimiters.
|
| @Delimiter | nvarchar(4000) | String of characters used to identify substring limits. |
| Column name | Data type | Description |
|---|---|---|
| RowID | int | Item Index |
| Text | nvarchar(MAX) | Item Content |
| StartPosition | int | Start Position |
Simple split by a space character:
|
1 2 3 4 5 6 7 8 9 10 11 |
DECLARE @Text nvarchar(MAX) DECLARE @Delimiter nvarchar(4000) SET @Text = 'The quick brown fox jumps over the lazy dog' SET @Delimiter = ' ' --one space character SELECT * FROM SQLHTTP.net.Splits(@Text, @Delimiter) |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
RowID Text StartPosition ------- --------- ------------- 1 The 1 2 quick 5 3 brown 11 4 fox 17 5 jumps 21 6 over 27 7 the 32 8 lazy 36 9 dog 41 |
Simple split by a two-character delimiter:
|
1 2 3 4 5 6 7 8 9 10 |
DECLARE @Text nvarchar(MAX) DECLARE @Delimiter nvarchar(4000) SET @Text = 'The quick brown fox jumps over the lazy dog' SET @Delimiter = 'e ' --two character delimiter SELECT * FROM SQLHTTP.net.Splits(@Text, @Delimiter) |
|
1 2 3 4 5 6 7 |
RowID Text StartPosition --------- ---------------------------------- ------------- 1 Th 1 2 quick brown fox jumps over th 5 3 lazy dog 36 |