字符串转数据表中数据的SQL函数
将用特殊字符分隔的字符串转换成一列数据表中的数据, 在某些情况下挺有用。
ALTER FUNCTION [dbo].[ConvertStringArrayToTable](@StringArray VARCHAR(MAX), @Delimiter CHAR(1))
RETURNS @StringArrayTable TABLE (Val VARCHAR(50))
AS
BEGIN
DECLARE @Delimiter_position INT
IF(RIGHT(@StringArray,1) != @Delimiter)
BEGIN
SET @StringArray = @StringArray + @Delimiter
END
WHILE(CHARINDEX(@Delimiter, @StringArray) <> 0)
BEGIN
SELECT @Delimiter_position = CHARINDEX(@Delimiter, @StringArray)
INSERT @StringArrayTable(Val)
VALUES (LEFT(@StringArray, @Delimiter_position - 1))
SELECT @StringArray = STUFF(@StringArray, 1, @Delimiter_position, '')
END
RETURN
END
RETURNS @StringArrayTable TABLE (Val VARCHAR(50))
AS
BEGIN
DECLARE @Delimiter_position INT
IF(RIGHT(@StringArray,1) != @Delimiter)
BEGIN
SET @StringArray = @StringArray + @Delimiter
END
WHILE(CHARINDEX(@Delimiter, @StringArray) <> 0)
BEGIN
SELECT @Delimiter_position = CHARINDEX(@Delimiter, @StringArray)
INSERT @StringArrayTable(Val)
VALUES (LEFT(@StringArray, @Delimiter_position - 1))
SELECT @StringArray = STUFF(@StringArray, 1, @Delimiter_position, '')
END
RETURN
END