直接加一个简单的存储过程,然后调用
CREATE FUNCTION dbo.SplitString
(
@String NVARCHAR(MAX),
@Delimiter CHAR(1)
)
RETURNS @Result TABLE (Value NVARCHAR(MAX))
AS
BEGIN
DECLARE @Value NVARCHAR(MAX)
WHILE CHARINDEX(@Delimiter, @String) > 0
BEGIN
SET @Value = SUBSTRING(@String, 1, CHARINDEX(@Delimiter, @String) - 1)
INSERT INTO @Result VALUES (@Value)
SET @String = SUBSTRING(@String, CHARINDEX(@Delimiter, @String) + 1, LEN(@String))
END
INSERT INTO @Result VALUES (@String)
RETURN
END
原先数据为
使用存储过程
SELECT Value AS user_id
FROM dbo.SplitString((SELECT user_id+ ',' FROM user FOR XML PATH('')), ',')
WHERE Value <> ''
返回结果