mysql - MS SQL函数的列表

我有如下varchar值

72,73,74

我试着用逗号分开，然后我想把上面的值转换成int，然后我想把Id和我的用户表匹配起来。

CREATE FUNCTION Fn_MyFunction(@MyUserIdValues VARCHAR(100))
RETURNS VARCHAR(300) AS
BEGIN

DECLARE @Result VARCHAR(300) = ''

Select UserName From UserTable
Where MyUserIdValues=UserIdValues


RETURN @Result

@结果必须如下所示

Joe,Michael,Ricky

任何帮助都将不胜感激。
谢谢。

最佳答案

做这个的经典方法。。。

/*
create table Users
(
    id int,
    name nvarchar(max)
)

insert into Users
values
(72, 'Joe'),
(73, 'Michael'),
(74, 'Ricky'),
(75, 'Manny'),
(76, 'Bob')

*/

CREATE FUNCTION dbo.Fn_MyFunction(@IdValues VARCHAR(100))
RETURNS NVARCHAR(max) AS
BEGIN
DECLARE @Result NVARCHAR(max);
DECLARE @delimiter as nchar = ',';

    WHILE LEN(@IdValues) <> 0
    BEGIN
        Declare @CurrentId int;
        If CHARINDEX(@delimiter, @IdValues) = 0
        begin
            Set @CurrentId = cast(@IdValues as int);
            Set @IdValues = ''
        End
        Else
        begin
            Set @CurrentId = cast(left(@IdValues,  charindex(@delimiter, @IdValues) -1)  as int)
            Set @IdValues = Substring(@IdValues,  charindex(@delimiter, @IdValues) +1, len(@IdValues))
        End
        select @Result = Isnull(@Result + ',', '') + Isnull((Select Name From Users Where Id=@CurrentId),'(unknown)')
   END

RETURN @Result
END
GO

Select dbo.Fn_MyFunction('72,73,74')
--Joe,Michael,Ricky
Select dbo.Fn_MyFunction('72,0,74')
--Joe,(unknown),Ricky
Select dbo.Fn_MyFunction('72,73,72,74,74')
--Joe,Michael,Joe,Ricky,Ricky