SQL 根据指定字符分隔字符串

DECLARE @Str VARCHAR(50)
SET @Str = 'test1,test2,test3'  
SELECT CONVERT(XML,'<r><a>'+REPLACE(@Str,',','</a><a>')+'</a></r>').query('r/a[1]').value('.','varchar(100)')
SELECT CONVERT(XML,'<r><a>'+REPLACE(@Str,',','</a><a>')+'</a></r>').query('r/a[2]').value('.','varchar(100)')
SELECT CONVERT(XML,'<r><a>'+REPLACE(@Str,',','</a><a>')+'</a></r>').query('r/a[3]').value('.','varchar(100)')