Parse comma separated list inline using CTE

Following SQL Server 2008 tsql statements uses Common Table Expression (CTE) to use parse the comma separated list inline.

DECLARE @List VARCHAR(8000)

SET @List = 'SQL Server,Oracle,DB2,MySQL,,MSAccess'

--SET @List = 'SQL Server,Oracle,DB2,MySQL,,MSAccess,'

--SET @List = 'SQL Server'

--SET @List = ''

;WITH ListValues AS (

SELECT SUBSTRING(@List+',', 1, CHARINDEX(',', @List+',', 1) - 1) AS Value,

CHARINDEX(',', @List+',', 1) CHARLOC

UNION ALL

SELECT SUBSTRING(@List+',', CHARLOC + 1, CHARINDEX(',', @List+',', CHARLOC + 1) - CHARLOC - 1 ) AS Value,

CHARINDEX(',', @List+',', CHARLOC+1) CHARLOC

FROM ListValues

WHERE CHARLOC < LEN(@List)

)

SELECT Value

FROM ListValues

This technic is much faster than the traditional technic that uses loop and temp table to store the values.

Tip: If using logic to separate the comma separated list inline, create a table valued function to reuse.