AdvancedSplit

Splits a string into one or more substrings (Advanced Version)

Free Function
SQL Server Compatibility: 2008, 2012, 2014, 2016, 2017

Syntax:

SELECT SQLHTTP.net.AdvancedSplit ( @Text, @Delimiter, @IsDelimiterCharArray, @RegexPattern, @Index )

Arguments:

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. 0 = Single delimiter composed of a one or more character string. 1 = Multiple delimiters, each composed of a single 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.
@Index	bigint	Integer value specifying the location of a substring



Return Type:

nvarchar(MAX)



Sample Usage:

Split by a space character and a simple regex pattern:


DECLARE @Text nvarchar(MAX)
DECLARE @Delimiter nvarchar(4000)
DECLARE @IsDelimiterCharArray bit
DECLARE @RegexPattern nvarchar(4000)
DECLARE @Index bigint

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'
SET @Index = 4

SELECT SQLHTTP.net.AdvancedSplit(@Text, @Delimiter, @IsDelimiterCharArray, @RegexPattern, @Index)

DECLARE @Text nvarchar(MAX)

DECLARE @Delimiter nvarchar(4000)

DECLARE @IsDelimiterCharArray bit

DECLARE @RegexPattern nvarchar(4000)

DECLARE @Index bigint

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'

SET @Index = 4

SELECT SQLHTTP.net.AdvancedSplit(@Text, @Delimiter, @IsDelimiterCharArray, @RegexPattern, @Index)


-----
dog

-----

dog

Split by a Char Array delimiter:


DECLARE @Text nvarchar(MAX)
DECLARE @Delimiter nvarchar(4000)
DECLARE @IsDelimiterCharArray bit
DECLARE @RegexPattern nvarchar(4000)
DECLARE @Index bigint

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
SET @Index = 5

SELECT SQLHTTP.net.AdvancedSplit(@Text, @Delimiter, @IsDelimiterCharArray, @RegexPattern, @Index)

DECLARE @Text nvarchar(MAX)

DECLARE @Delimiter nvarchar(4000)

DECLARE @IsDelimiterCharArray bit

DECLARE @RegexPattern nvarchar(4000)

DECLARE @Index bigint

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

SET @Index = 5

SELECT SQLHTTP.net.AdvancedSplit(@Text, @Delimiter, @IsDelimiterCharArray, @RegexPattern, @Index)


-----
ox

-----



AdvancedSplit

Featured API Wrapper Code / SQL Server