STRING_SPLIT function in SQL Server 2016

This function is introduced in the SQL server 2016. The purpose of this built-in string function is to convert string arrays to columns which are separated by any separator.

Syntax

STRING_SPLIT (string, separator)

Where string is a character delimited string of type CHAR, VARCHAR, NVARCHAR and NCHAR.

The separator is a single character delimiter by which the input string need to be split. The separator character can be one of the type:  CHAR (1), VARCHAR (1), NVARCHAR (1) and NCHAR (1).

Result of this function is a table with one column with column name as value.

Where we can use in real time

Suppose we have an application which manage the product type.

   

 

When we click on the save UI will send the value like below

Product_categoy=’Accessories’ , Product_sub_category_Name=’Bike Racks’ and selected_product_name=’HL Mountain Handlebars,HL Road Handlebars,HL Touring Handlebars,LL Mountain Handlebars,LL Road Handlebars’

And we want to store these data in row by row as below.

                         

 

Before SQL server 2016 for this scenario we need to write the user-defined function.

Read: Splita string to a table using T-SQL

http://bageshkumarbagi-msbi.blogspot.com/2017/07/split-string-to-table-using-t-sql.html

In the sql server 2016 a new build in function STRING_SPLIT to split the string.

I am creating a table where I am storing these values.

CREATE TABLE [dbo].[Project_Tran](     ID int identity (1,1),                 [Product_Category] [nvarchar](50) NOT NULL,                 [Product_Sub_Category_Name] [nvarchar](50) NOT NULL,                 [Product_Name] [nvarchar](50) NOT NULL )

Now I am inserting records in this table

declare  @table table (Project_Name varchar(100)) insert into @table select value from string_split('HL Mountain Handlebars,HL Road Handlebars,HL Touring Handlebars,LL Mountain Handlebars,LL Road Handlebars',',') insert into Project_Tran select pc.EnglishProductCategoryName As Product_Category, psc.EnglishProductSubcategoryName AS Product_Sub_Category_Name, p.EnglishProductName As Product_Name FROM  DimProduct P INNER Join @table t on t.Project_Name=p.EnglishProductName inner join DimProductSubcategory psc on p.ProductSubcategoryKey=psc.ProductCategoryKey Inner Join DimProductCategory pc on pc.ProductCategoryKey=psc.ProductCategoryKey where pc.EnglishProductCategoryName ='Accessories' and psc.EnglishProductSubcategoryName='Bike Racks' Group by pc.EnglishProductCategoryName,psc.EnglishProductSubcategoryName, p.EnglishProductName

Executing this script

                       

See the records in the table

    

This build in function makes our life easy.