Split on
]
instead of comma, replace the
[
and handle the comma at the start of an item if it exists...
DECLARE @x nvarchar(max) = '[abc],[cde,cdf]'
;with q as
(
select REPLACE(val, '[','') as val2
from dbo.Split(@x, ']')
where isnull(val,'') <> ''
)
select case when SUBSTRING(val2,1,1)=',' THEN SUBSTRING(val2, 2, LEN(val2))
else val2 END
from q
I only used a CTE (or a sub-query would do it) to avoid multiple calls to the split function.