DECLARE @csv varchar(10)= '1,2,3'
SELECT @CSV CSVData
SELECT p.q.value('.', 'varchar(100)') as word
FROM (SELECT CAST('<Values><Value>' + REPLACE( @csv, ',', '</Value><Value>')
+ '</Value></Values>' AS xml) AS m)t
CROSS APPLY m.nodes('/Values/Value')p(q)
OUTPUT1:
CSVData
1,2,3
OUTPUT2:
Word
1
2
3