将逗号分隔的ID替换成Name的函数

效果：

有表如：

有字段：

　　 '1,3,5'

要求输出

　　'张三,王二,DBA谭'
测试代码

--测试数据
CREATE TABLE TTest(ID int identity, name varchar(50))
insert into TTest select '张三'
insert into TTest select '李四'
insert into TTest select '王二'
insert into TTest select '麻子'
insert into TTest select 'DBA谭'

go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
-- =============================================
-- Author:        DBA谭
-- Create date: 2014-05-06
-- Description:    
-- =============================================
*/
CREATE FUNCTION fn_IndexToName 
(
    @p1 VARCHAR(100)
)
RETURNS NVARCHAR(500)
AS
BEGIN
    DECLARE @Result NVARCHAR(500)
    IF charindex(',',@p1) = 0
    begin
        SELECT @Result = Name FROM TTest WHERE ID = @p1
    end
    else
    begin
        set @Result = ',' + @p1 + ','
        SELECT @Result = REPLACE(@Result, ',' + CAST(ID AS VARCHAR(10)) + ',',','+ Name + ',') 
            FROM TTest WHERE @Result like '%,'+CAST(ID AS VARCHAR(10)) + ',%'
        SELECT @Result = RIGHT(LEFT(@Result,LEN(@Result)-1),LEN(LEFT(@Result,LEN(@Result)-1))-1)
    end
    RETURN @Result
    

END
GO    

select '1,3,5' ID列,dbo.fn_IndexToName('1,3,5') Name列


drop table TTest

View Code

 

转载于:https://www.cnblogs.com/tanleittl/p/3713670.html