SQL - STRING_SPLIT

ProductId	Name	Tags
1	Full-Finger Gloves	clothing,road,touring,bike
2	LL Headset	bike
3	HL Mountain Frame	bike,mountain

SELECT ProductId, Name, value 

FROM Product 

CROSS APPLY STRING_SPLIT(Tags, ','); 

ProductId	Name	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
3	HL Mountain Frame	bike
3	HL Mountain Frame	mountain

Aggregation by values

SELECT value as tag, COUNT(*) AS [Number of articles] 

FROM Product 

    CROSS APPLY STRING_SPLIT(Tags, ',') 

GROUP BY value 

HAVING COUNT(*) > 2 

ORDER BY COUNT(*) DESC; 

Search by tag value

To find products with a single tag (clothing):

SQLCopy

SELECT ProductId, Name, Tags 

FROM Product 

WHERE 'clothing' IN (SELECT value FROM STRING_SPLIT(Tags, ',')); 

Find products with two specified tags (clothing and road):

SQLCopy

SELECT ProductId, Name, Tags 

FROM Product 

WHERE EXISTS (SELECT * 

    FROM STRING_SPLIT(Tags, ',') 

    WHERE value IN ('clothing', 'road'));

Find rows by list of values

Developers must create a query that finds articles by a list of IDs. They can use following query:

SQLCopy

SELECT ProductId, Name, Tags 

FROM Product 

JOIN STRING_SPLIT('1,2,3',',')

    ON value = ProductId; 

The preceding STRING_SPLIT usage is a replacement for a common anti-pattern. Such an anti-pattern can involve the creation of a dynamic SQL string in the application layer or in Transact-SQL. Or an anti-pattern can be achieved by using the LIKE operator. See the following example SELECT statement:

SQLCopy

SELECT ProductId, Name, Tags 

FROM Product 

WHERE ',1,2,3,' LIKE '%,' + CAST(ProductId AS VARCHAR(20)) + ',%'; 

Split comma-separated value string

Parse a comma-separated list of values and return all non-empty tokens:

SQLCopy

DECLARE @tags NVARCHAR(400) = 'clothing,road,,touring,bike' 

 

SELECT value 

FROM STRING_SPLIT(@tags, ',') 

WHERE RTRIM(value) <> '';