字符串与表间的简单转换
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
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
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('')
FROM dbo.ConvertStringToTable('1,2,3',',')
FOR XML PATH('')