NEW T-SQL FEATURES IN SQL SERVER 2016- II [ STRING_SPLIT() ]

In this post we shall check out the new features Microsoft introduced in SQL Server 2016.

Splits the character expression using specified separator. STRING_SPLIT takes a string that should be divided and the separator that will be used to divide string. It returns a single-column table with substrings.

Notes –

1. If the input string is NULL, the STRING_SPLIT table-valued function returns an empty table.
2. STRING_SPLIT requires at least compatibility mode 130.
3. STRING_SPLIT will return empty string if there is nothing between separator. Condition RTRIM(value) ” will remove empty tokens.

Sample example for STRING_SPLIT function

Generate Sample Data

--

USE [Pawan]
GO
/****** Object:  Table [dbo].[SplitStrings]    Script Date: 1/7/2018 9:36:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SplitStrings](
	[Id] [int] NULL,
	[Vals] [varchar](100) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[SplitStrings] ([Id], [Vals]) VALUES (1, N'a,b')
GO
INSERT [dbo].[SplitStrings] ([Id], [Vals]) VALUES (2, N'a')
GO
INSERT [dbo].[SplitStrings] ([Id], [Vals]) VALUES (3, N'b,3')
GO
INSERT [dbo].[SplitStrings] ([Id], [Vals]) VALUES (4, N'c,u')
GO


--

TableData

Output Required

Query

--

SELECT Id,Vals,Value FROM 
splitstrings
CROSS APPLY 
(
	SELECT Value 
	FROM STRING_SPLIT(Vals, ','))
p

                                    
--

Output

--

/*------------------------
SELECT Id,Vals,Value FROM 
splitstrings
CROSS APPLY 
(
	SELECT Value 
	FROM STRING_SPLIT(Vals, ','))
p
------------------------*/
Id          Vals          Value
----------- ----------------------------
1           a,b           a
1           a,b           b
2           a             a
3           b,3           b
3           b,3           3
4           c,u           c
4           c,u           u

(7 row(s) affected)


                                    
--

The STRING_SPLIT function is available only under compatibility level 130. If your database compatibility level is lower than 130, SQL Server will not be able to find and execute STRING_SPLIT function. The error you will get is ‘STRING_SPLIT’ is not a recognized built-in function name

You can change a compatibility level of database using the following command:
ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130

--

ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130
                                    
--

Refer below for more details-

Well there are many methods by which you can split the string like XML,Numbers table,CLR method, SQL function, JSON etc. Aaron Bertrand(https://sqlperformance.com/author/abertrand) has performed detailed comparison on these methods and clearly STRING_SPLIT() is the best method we have got. So going forward always use STRING_SPLIT() method for any split strings work.

Refer below for more details-

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com