DECLARE @str NVARCHAR(MAX);
SET @str = ‘aa|bb|cc|dd‘;
DECLARE @n NVARCHAR(100)
SET @str += ‘|‘
WHILE LEN(@str) > 1
    BEGIN
        SET @n = LEFT(@str, CHARINDEX(‘|‘, @str))
        SET @str = RIGHT(@str, LEN(@str) - LEN(@n))
        SET @n = REPLACE(@n, ‘|‘, ‘‘)
        SELECT  @n
    END

sql分割字符串[转载]