将逗号分隔的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