Applicability Of Cross Apply with STRING_SPLIT in SQL Server

Introduction

SQL Server 2016, has introduced the STRING_SPLIT function.This function splits the string using specified delimiter.

SQL Server 2005 introduced the APPLY operator which is use to join a table to a table-valued function.The CROSS APPLY operator returns only those rows from left table expression if that matches with right table expression.

In this article, we will present the applicability Of Cross Apply with STRING_SPLIT by providing some examples.

Example 1: Extract First and Last Name

Let us first create a table as under

DECLARE @T TABLE(Names VARCHAR(100))
INSERT INTO @T 
SELECT 'Niladri' UNION ALL 
SELECT 'Niladri/Biswas' UNION ALL 
SELECT 'RNA/ Team' UNION ALL 
SELECT 'Dotnet /Funda'

SELECT *
FROM @T

/*
Names
---------
Niladri
Niladri/Biswas
RNA/ Team
Dotnet /Funda
*/

The objective is to split the Names into First and Last Name columns by using Cross Apply and STRING_SPLIT.

As a first step, we will split the Names column values by using the STRING_SPLIT function as shown below

DECLARE @T TABLE(Names VARCHAR(100))
INSERT INTO @T 
SELECT 'Niladri' UNION ALL 
SELECT 'Niladri/Biswas' UNION ALL 
SELECT 'RNA/ Team' UNION ALL 
SELECT 'Dotnet /Funda'

--Split the Names column values by using the STRING_SPLIT function
SELECT f.*
FROM
(SELECT 
	Names
FROM @T)X
CROSS APPLY
(
	SELECT 
		Rn=ROW_NUMBER() Over(Partition by X.Names Order by X.Names)
		,X.Names
		,ExtractedValuesFromNames = value
	FROM STRING_SPLIT(X.Names,'/') AS D
)f

/*

Rn	Names		ExtractedValuesFromNames
--	-----		-------------------------
1	Niladri			Niladri
1	Niladri/Biswas		Niladri
2	Niladri/Biswas		Biswas
1	RNA/ Team		RNA
2	RNA/ Team	 	Team
1	Dotnet /Funda		Dotnet 
2	Dotnet /Funda		Funda

*/

The CROSS APPLY operator joins the values of the Names column from @T and it is passed to the STRING_SPLIT function per row which splits the value and produces a table values function on the fly.

From the output, it can be figure out that, the rows for which Rn=1, are the candidates for First Name column while for those Rn=2, are the candidates for Last Name column.

As a last step, we have to transform the rows to columns (both for First Name and Last Name) pertaining to the Names as under

SELECT
		Names
        	,FirstName = 
					STUFF((	SELECT '' + 
							c2.ExtractedValuesFromNames + ' ' 
							FROM splitNamesCTE c2
							WHERE (c2.Names = c1.Names)
							and c2.Rn=1 -- candidates for First Name
							FOR XML PATH('')),1,0,'')
		,LastName = 
					STUFF((	SELECT '' + 
							c2.ExtractedValuesFromNames + ' ' 
							FROM splitNamesCTE c2
							WHERE (c2.Names = c1.Names)
							and c2.Rn=2 -- candidates for Last Name
							FOR XML PATH('')),1,0,'')
FROM splitNamesCTE c1
GROUP BY c1.Names

The complete query is provided below

DECLARE @T TABLE(Names VARCHAR(100))
INSERT INTO @T 
SELECT 'Niladri' UNION ALL 
SELECT 'Niladri/Biswas' UNION ALL 
SELECT 'RNA/ Team' UNION ALL 
SELECT 'Dotnet /Funda'

--Split the Names column values by using the STRING_SPLIT function
;WITH splitNamesCTE AS(
SELECT f.*
FROM
(SELECT 
	Names
FROM @T)X
CROSS APPLY
(
	SELECT 
		Rn=ROW_NUMBER() Over(Partition by X.Names Order by X.Names)
		,X.Names
		,ExtractedValuesFromNames = value
	FROM STRING_SPLIT(X.Names,'/') AS D
)f)

SELECT
		Names
        ,FirstName = 
					STUFF((	SELECT '' + 
							c2.ExtractedValuesFromNames + ' ' 
							FROM splitNamesCTE c2
							WHERE (c2.Names = c1.Names)
							and c2.Rn=1 -- candidates for First Name
							FOR XML PATH('')),1,0,'')
		,LastName = 
					STUFF((	SELECT '' + 
							c2.ExtractedValuesFromNames + ' ' 
							FROM splitNamesCTE c2
							WHERE (c2.Names = c1.Names)
							and c2.Rn=2 -- candidates for Last Name
							FOR XML PATH('')),1,0,'')
FROM splitNamesCTE c1
GROUP BY c1.Names

/*

Names		FirstName		LastName
-----		---------		--------
Dotnet /Funda	Dotnet  		Funda 
Niladri		Niladri 		NULL
Niladri/Biswas	Niladri 		Biswas 
RNA/ Team	RNA 	 		Team 

*/

Example 2: Number Generation

Suppose we have 1,2,4-8,9,10,11-14,16. We need to generate the output as

Numbers
--------
1
2
4
5
6
7
8
9
10
11
12
13
14
16

As a first step let us split the records and segregate them as under

DECLARE @data VARCHAR(50) = '1,2,4-8,9,10,11-14,16'

;WITH segregateRecords AS(
SELECT
	value
	,CheckForRange = IIF(value Like '%-%','Generate Range','Scalar')	
FROM STRING_SPLIT(@data,','))

SELECT *
FROM segregateRecords

/*
value	CheckForRange
-----	-------------
1	Scalar
2	Scalar
4-8	Generate Range
9	Scalar
10	Scalar
11-14	Generate Range
16	Scalar

*/

Next step is to generate the Sequential Numbers for the Ranges (Generate Range) as under

SELECT number
FROM generateNumbersCTE
CROSS APPLY STRING_SPLIT
(
	(
		SELECT 
		STUFF((	SELECT  
			',' + c2.value 
		FROM segregateRecords c2
		WHERE c2.CheckForRange='Generate Range'
		FOR XML PATH('')),1,1,'')
	),',') AS S
WHERE number 
BETWEEN PARSENAME(REPLACE(value, '-', '.'), 2) AND PARSENAME(REPLACE(value, '-', '.'), 1)

The inner subquery

SELECT 
STUFF((	SELECT  
			',' + c2.value 
		FROM segregateRecords c2
		WHERE c2.CheckForRange='Generate Range'
		FOR XML PATH('')),1,1,'')

transforms the Generate Range values into a scalar one

4-8,11-14

The generateNumbersCTE generates a sequential range of numbers

,generateNumbersCTE(number) AS(

	SELECT 1
    	UNION ALL
    	SELECT number+1 FROM generateNumbersCTE WHERE number < 20 --generating 20 sequential numbers

)

It is the CROSS APPLIED with the STRING_SPLIT and for each row, the number range is formed from the generateNumbersCTE Common Table Expression(CTE).

As a final step, we have to merge all the records

--Project the final Records
SELECT Numbers = value FROM segregateRecords WHERE CheckForRange = 'Scalar'
UNION ALL
SELECT * FROM generateSequentialNumbersForRanges
ORDER BY 1

The complete query with the final result is provided below

DECLARE @data VARCHAR(50) = '1,2,4-8,9,10,11-14,16'

--Step 1: Segregate Records
;WITH segregateRecords AS(
SELECT
	value
	,CheckForRange = IIF(value Like '%-%','Generate Range','Scalar')	
FROM STRING_SPLIT(@data,','))
--Step 2: Generate sequential Numbers(20)
,generateNumbersCTE(number) AS(

	SELECT 1
    UNION ALL
    SELECT number+1 FROM generateNumbersCTE WHERE number < 20 --generating 20 sequential numbers

)
--Step 3: Generate the Sequential Numbers for the Ranges
,generateSequentialNumbersForRanges AS(
SELECT number
FROM generateNumbersCTE
CROSS APPLY STRING_SPLIT
(
	(
		SELECT 
		STUFF((	SELECT  
			',' + c2.value 
		FROM segregateRecords c2
		WHERE c2.CheckForRange='Generate Range'
		FOR XML PATH('')),1,1,'')
	),',') AS S
WHERE number 
BETWEEN PARSENAME(REPLACE(value, '-', '.'), 2) AND PARSENAME(REPLACE(value, '-', '.'), 1))

--Project the final Records
SELECT Numbers = value FROM segregateRecords WHERE CheckForRange = 'Scalar'
UNION ALL
SELECT * FROM generateSequentialNumbersForRanges
ORDER BY 1

/*
Numbers
--------
1
2
4
5
6
7
8
9
10
11
12
13
14
16

*/

Example 3: Alphabet Generation

Along the line of the Example 2, we can do the same thing for alphabets as described below

DECLARE @data VARCHAR(50) = 'a,b,c-e,g,h,i-l,x,y,z'

;WITH segregateRecords AS(
SELECT
	value
	,CheckForRange = IIF(value Like '%-%','Generate Range','Scalar')	
FROM STRING_SPLIT(@data,','))
--Step 2: Generate sequential Numbers(from 97 to 122)
,generateNumbersCTE(number) AS(

	SELECT 97 --ASCII for a
    UNION ALL
    SELECT number+1 FROM generateNumbersCTE WHERE number < 122 --ASCII for z

)
--Step 3: Generate the Sequential Numbers for the Ranges
,generateSequentialNumbersForRanges AS(
SELECT number
FROM generateNumbersCTE
CROSS APPLY STRING_SPLIT
(
	(
		SELECT 
				STUFF((	SELECT  
					',' + c2.value 
				FROM 

				(
					SELECT
						value = CAST(ASCII(PARSENAME(REPLACE(value, '-', '.'), 2)) AS VARCHAR(3)) + '-' + CAST(ASCII(PARSENAME(REPLACE(value, '-', '.'), 1)) AS VARCHAR(3))
						FROM segregateRecords c2
					WHERE c2.CheckForRange='Generate Range'
				) c2		
				FOR XML PATH('')),1,1,'')
	),',') AS S 
WHERE number 
BETWEEN PARSENAME(REPLACE(value, '-', '.'), 2) AND PARSENAME(REPLACE(value, '-', '.'), 1))

--Project the final Records
SELECT Alphabets = value FROM segregateRecords WHERE CheckForRange = 'Scalar'
UNION ALL
SELECT CHAR(number) FROM generateSequentialNumbersForRanges
ORDER BY 1

/*

Alphabets
---------
a
b
c
d
e
g
h
i
j
k
l
x
y
z

*/

Conclusion

Hope this article will be helpful. We have learnt how to use CROSS APPLY with SQL Server 2016 STRING_SPLIT function. Thanks for reading. Zipped file attached.