自定义函数查询(1,2,3,4)返回对应ID的name
/****** Object: UserDefinedFunction [dbo].[GetUserName] Script Date: 11/13/2013 09:27:13 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[GetUserName] (@str nvarchar(max), --字符串 @spliter nvarchar(10)) --分割符 returns nvarchar(max)--返回构造好的Account_Name AS BEGIN DECLARE @Result nvarchar(max) DECLARE @UserName nvarchar(64) DECLARE @Num int DECLARE @Pos int DECLARE @NextPos int SET @Num = 0 SET @Pos = 1 SET @Result='' WHILE(@Pos <= LEN(@str)) BEGIN SELECT @NextPos = CHARINDEX(@spliter, @str, @Pos) IF (@NextPos = 0 OR @NextPos IS NULL) SELECT @NextPos = LEN(@str) + 1 SELECT @UserName=Account_Name FROM Biz_Account WHERE Account_ID=RTRIM(LTRIM(SUBSTRING(@str, @Pos, @NextPos - @Pos))) SET @Result = @Result + @UserName +',' SELECT @Pos = @NextPos+1 END RETURN @Result END