AdvancedSplitCount

Returns the highest available Index that can be used using the Split function (Advanced Version)

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

Syntax:

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

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.



Return Type:

int



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)

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 SQLHTTP.net.AdvancedSplitCount(@Text, @Delimiter, @IsDelimiterCharArray, @RegexPattern)

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 SQLHTTP.net.AdvancedSplitCount(@Text, @Delimiter, @IsDelimiterCharArray, @RegexPattern)

-----

Split by a Char Array delimiter:


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 SQLHTTP.net.AdvancedSplitCount(@Text, @Delimiter, @IsDelimiterCharArray, @RegexPattern)

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 SQLHTTP.net.AdvancedSplitCount(@Text, @Delimiter, @IsDelimiterCharArray, @RegexPattern)

-----



AdvancedSplitCount

Featured API Wrapper Code / SQL Server