22,210
社区成员
发帖
与我相关
我的任务
分享
IF OBJECT_ID('tempdb..tmp_1') IS NOT NULL DROP TABLE tmp_1
CREATE TABLE tmp_1(id INT,[name] NVARCHAR(100))
INSERT INTO tmp_1
SELECT 2000,'A001' UNION ALL
SELECT 3601,'B007' UNION ALL
SELECT 3688,'C089'
CREATE FUNCTION fn_test(@str VARCHAR(100))
RETURNS NVARCHAR(2000)
AS
BEGIN
DECLARE @name NVARCHAR(2000);
SET @name = STUFF( (
SELECT ',' + CAST([name] AS VARCHAR)
FROM (
SELECT DISTINCT SUBSTRING(a.id, b.number, CHARINDEX(',', a.id+',', b.number)-b.number) AS id
FROM (SELECT @str AS id) a
JOIN MASTER.dbo.spt_values b
ON CHARINDEX(',', ','+a.id, b.number) = b.number
WHERE b.[type]= 'P'
) aa
INNER JOIN tmp_1 bb ON bb.id=CAST(aa.id AS INT)
ORDER BY [name]
FOR XML PATH('')),1,1,'');
RETURN @name
END
SELECT dbo.fn_test('2000,3601')