TSQL String Split

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

DECLARE @tags NVARCHAR(400) = 'clothing,road,,touring,bike'  
  
SELECT value  
FROM STRING_SPLIT(@tags, ',')  
WHERE RTRIM(value) <> '';

STRING_SPLIT will return empty string if there is nothing between separator. Condition RTRIM(value) <> ” will remove empty tokens.

Product table has a column with comma-separate list of tags shown in the following example:

Following query transforms each list of tags and joins them with the original row.

SELECT ProductId, Name, value  
FROM Product  
    CROSS APPLY STRING_SPLIT(Tags, ',');

Here is the result set.

SELECT DISTINCT 
	LTRIM(RTRIM(value)) AS Tag  
FROM 
	tblData 
	CROSS APPLY STRING_SPLIT(Tags, ',');