字符串与表间的简单转换

 

CREATE FUNCTION [dbo].[ConvertStringToTable](@stringArray NVARCHAR(MAX), @delimiter CHAR(1))
    RETURNS @StringArrayTable TABLE (id INT IDENTITY(1,1) NOT NULL,val NVARCHAR(255))
AS
BEGIN
    DECLARE @delimiterPosition INT
    
    IF(RIGHT(@stringArray,1) != @delimiter)
    BEGIN
        SET @stringArray = @stringArray + @delimiter
    END

    WHILE(CHARINDEX(@delimiter, @stringArray) <> 0)
    BEGIN
        SELECT @delimiterPosition = CHARINDEX(@delimiter, @stringArray)
        
        INSERT @StringArrayTable(val)
        VALUES (LEFT(@stringArray, @delimiterPosition - 1))
        SELECT @stringArray = STUFF(@stringArray, 1, @delimiterPosition, '')
    END

    RETURN
END

 

 逆过程：

SELECT val+','
FROM dbo.ConvertStringToTable('1,2,3',',')
FOR XML PATH('')