SQL Server Portal

In SQL Server 2022, an enhancement came in STRING_SPLIT() function, which was a long awaited functionality. This enhancement provides a new column name ordinal, which has row number for each string split by this function.

Let me create a sample to demonstrate the functionality of new ordinal column in STRING_SPLIT() function as shown below.

Sample:

USE tempdb
GO
   
CREATE TABLE #Employee
(
 EmployeeID     INT IDENTITY(1,1),
 EmployeeName   VARCHAR(500),
 EmailAddresses VARCHAR(500)
);
GO
   
INSERT INTO #Employee(EmployeeName, EmailAddresses)
VALUES
('John', 'John_1@gmail.com;John_2@gmail.com;John_3@hotmail.com'),
('Sarah', 'Sarah_1@gmail.com;Sarah_2@hotmail.com;Sarah_3@gmail.com'),
('Aaron', 'Aaron_1@gmail@com;Aaron_2@hotmail.com'),
('Ackerman', 'Ackerman_1@gmail.com;Ackerman_2@hotmail.com');
GO

SELECT * FROM #Employee;
GO
--OUTPUT

Example 1:

In the below example, I will split the string in email addresses column based on a separator as usual. Also, I will pass the third argument (which came in SQL 2022 as an ehancement) as 1 in STRING_SPLIT() function which means that STRING_SPLIT() will not only split the string but also provide a serial number (ordinal column) against each split string as shown below.

USE tempdb
GO

SELECT EmployeeID
     , EmployeeName
 , value AS EmailAddress
 , Ordinal
FROM   #Employee
CROSS APPLY STRING_SPLIT(EmailAddresses, ';', 1);
GO
--OUTPUT

Example 2:

In this example, I will show you how important is this new column ordinal and how we used to struggle in earlier version of SQL Server (Earlier than 2022) to mimic the same functionality with the help of common table expression & other functions.

Let me grab the first two email addresses for each employee using old and new approach. Both approaches will return the same result but new approach is simple & efficient.

• Old Approach: (Using ROW_NUMBER() & common table expression)

USE tempdb
GO
 
;WITH CTE AS
(SELECT EmployeeID
      , EmployeeName
      , value AS EmailAddress
      , ROW_NUMBER() OVER(PARTITION BY EmployeeID 
						  ORDER BY EmployeeID ASC) AS Ordinal
FROM    #Employee
CROSS APPLY STRING_SPLIT(EmailAddresses, ';'))
SELECT * FROM CTE WHERE Ordinal<3;
GO
--OUTPUT

• New Approach: (Using ordinal column)

USE tempdb
GO
 
SELECT EmployeeID
     , EmployeeName
     , value AS EmailAddress
	 , Ordinal
FROM   #Employee
CROSS APPLY STRING_SPLIT (EmailAddresses, ';', 1)
WHERE ordinal<3;
GO
--OUTPUT

Conclusion:

I found the new enhancement of STRING_SPLIT() function very useful, earlier we used common table expression & ROW_NUMBER() functions to find the row number of each split string but now we can easily achieve with the help of STRING_SPLIT() ordinal column. Do let me know if you use ordinal column and how did you find it.

Read Full Post »