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.
