Saturday, June 18, 2016

SQL- String_Split() function

This is the most awaited feature of SQL Server which is coming with SQL Server 2016. This allows to get rid of previous perversions with XML and CTE. This function returns a single-column table with fragments after the input character string is splitted by the specified separator.
Syntax
STRING_SPLIT (String, Separator) 
Arguments
String: Is an expression of any character type (i.e. nvarchar, varchar, nchar or char).
Separator: Is a single character expression of any character type (e.g. nvarchar(1), varchar(1), nchar(1) or char(1)) that is used as separator for concatenated strings.
Return Types: Returns a single-column table with fragments. The name of the column is value. The length of the return type is the same as the length of the string argument.
Important Facts: There are some facts about this function as –
  1. Default return data type is varchar and it will return nvarchar if any of the input arguments are either nvarchar or nchar.
  2. It will return an empty table If the input string is NULL.
  3. It will return empty string if there is nothing between separators. Condition RTRIM(value) <> '' will remove empty tokens.
  4. STRING_SPLIT requires at least compatibility mode 130.
  5. If your database compatibility level is lower than 130, SQL Server will not be able to find and execute STRING_SPLIT function.
  6. We can change the compatibility level of database as following: ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130

Examples
Split comma separated value stringParse a comma separated list of values and return all tokens:
----Local Variables
Declare @StringValue Varchar(200)
Set @StringValue='Ryan Arjun, Kimmy Wany, Rosy Gray,,Johnson Blue'

---- Separator Variable
Declare @Separator char(1)=','

---- Using String Split function
Select Value
from string_split(@StringValue,@Separator)
Value
Ryan Arjun
Kimmy Wany
Rosy Gray

Johnson Blue
STRING_SPLIT will return empty string if there is nothing between separator.
Split comma separated value string- Parse a comma separated list of values and return all non-empty tokens:
----Local Variables
Declare @StringValue Varchar(200)
Set @StringValue='Ryan Arjun, Kimmy Wany, Rosy Gray,,Johnson Blue'

---- Separator Variable
Declare @Separator char(1)=','

---- Using String Split function
Select Value
from string_split(@StringValue,@Separator)
---- remove empty strings from output
where RTRIM(value) <> ''; 
Value
Ryan Arjun
Kimmy Wany
Rosy Gray
Johnson Blue
Split comma separated value string in a column
To understand the functionality, we are using the table variable and fill some data values with tags column and using the String_Split() function to get the expected result as given below: 
----Declare table variable
Declare @ProductTable Table
(
ProdId Int,
ProductName varchar(50),
Tags Varchar(200)
)

---- Fill Data into table variable
Insert into @ProductTable (ProdId, ProductName, Tags)
Values (1, 'Full-Finger Gloves', 'clothing,road,touring,bike'),
(2,'LL Headset','bike, cycle'),
(3,'HL Mountain Frame','bike,mountain,weather')

---- Pull values from table Variable
SELECT ProdId, ProductName, Tags
FROM @ProductTable 
ProdId
ProductName
Tags
1
Full-Finger Gloves
clothing,road,touring,bike
2
LL Headset
bike, cycle
3
HL Mountain Frame
bike,mountain,weather


---- Separator Variable
Declare @Separator char(1)=','

---- Using String Split function
SELECT ProdId, ProductName, Value 
FROM @ProductTable 
CROSS APPLY
STRING_SPLIT(Tags, @Separator); 
ProdId
ProductName
Value
1
Full-Finger Gloves
clothing
1
Full-Finger Gloves
road
1
Full-Finger Gloves
touring
1
Full-Finger Gloves
bike
2
LL Headset
bike
2
LL Headset
 cycle
3
HL Mountain Frame
bike
3
HL Mountain Frame
mountain
3
HL Mountain Frame
weather

----- Aggregation by values
SELECT value as tag, COUNT(*) AS [Products] 
FROM @ProductTable 
    CROSS APPLY STRING_SPLIT(Tags, ',') 
GROUP BY value  HAVING COUNT(*) >
ORDER BY COUNT(*) DESC;
----shows the number of products per each tag, ordered by number of products, and to filter only the tags with more than 2 products.
Tag
Products
bike
3

----- Search by tag value: To find products with a single tag
SELECT ProdId, ProductName, Tags 
FROM @ProductTable 
WHERE 'bike'
IN (SELECT value FROM STRING_SPLIT(Tags, ','));
ProdId
ProductName
Tags
1
Full-Finger Gloves
clothing,road,touring,bike
2
LL Headset
bike, cycle
3
HL Mountain Frame
bike,mountain,weather

----- Search by tag value: To find products with a multiple tags
SELECT ProdId, ProductName, Tags 
FROM @ProductTable 
WHERE EXISTS (SELECT * 
    FROM STRING_SPLIT(Tags, ',') 
    WHERE value IN ('clothing', 'mountain')); 
ProdId
ProductName
Tags
1
Full-Finger Gloves
clothing,road,touring,bike
3
HL Mountain Frame
bike,mountain,weather
Conclusion
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. This function works only with a single-character delimiter and you can use it anywhere in your SQL or dynamic SQL Scripts also.

No comments:

Post a Comment

Popular Posts

Get Sponsored by Big Brands