I was going through a couple of emails this morning when I found one from Pinal Dave. It was exposing a really nifty T-SQL trick that I will surely used very soon.

Very often, we have a CSV field in a database and we want to split on the separator. This can be done easily. Have a look at this snippet:

DECLARE @t TABLE
(
EmployeeID INT,
Certs VARCHAR(8000)
 )
INSERT @t VALUES (1,'B.E.,MCA, MCDBA, PGDCA'), (2,'M.Com.,B.Sc.'), (3,'M.Sc.,M.Tech.')

SELECT * FROM @t

SELECT EmployeeID, LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS Certs
FROM
(
	SELECT EmployeeID,CAST('' + REPLACE(Certs,',','') + '' AS XML) AS x
	FROM   @t
) AS t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)

The snippet starts by creating an in-memory table with some CSV values. then the trick is to convert this field to an XML value which makes it easier to access each value inside it.

If you have a small amount of rows, that trick can really help you but if you have a large result set this same trick could hurt you because we all know that SQL Server is not really at its best with XML!

BTW, this trick was found on http://blog.sqlauthority.com/2015/04/21/sql-server-split-comma-separated-list-without-using-a-function/ which also link to other ways of achieving the same thing using a stored proc and a UDF.