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.
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.
