sql - 在 concat text 我得到 NULL

Question

我尝试 concat 2 字符串并将其反转，但结果我得到 NULL 而不是 ex 的文本。第一个字符串“a1” 第二个字符串“b2” 结果“21ba”

CREATE function addToString(@val1 varchar(100),@val2 varchar(100))
returns varchar(200)
as
begin
    declare @i as int
    declare @output as varchar(200)
    if len(@val1)!=len(@val2)
        set @output='length error: '+cast(len(@val1) as varchar(10))+
        '<>'+cast(len(@val2) as varchar(10)) 
    else
        select @i=len(@val1)-1
        while @i>=0
            begin
                set @output= concat(@output,substring(@val2,@i,1))
                set @output= concat(@output,substring(@val1,@i,1))  
                set @i=@i-1
            end
    return @output
end
go
select dbo.[addToString]('a1','b2') as txt

标签： sqlsql-serverconcatenationvarchar

Answer 1

你的逻辑是错误的，你没有初始化@output：

CREATE function addToString(@val1 varchar(100),@val2 varchar(100))
returns varchar(200)
as
begin
    declare @i as int

 -- declare @output as varchar(200) -- initialized to NULL
    declare @output as varchar(200) = ''

    if len(@val1)!=len(@val2)
        set @output='length error: '+cast(len(@val1) as varchar(10))+
        '<>'+cast(len(@val2) as varchar(10)) 
    else
     -- select @i=len(@val1)-1 -- strips the last char
        select @i=len(@val1)

     -- while @i>=0
        while @i>0

            begin
                set @output= concat(@output,substring(@val2,@i,1))
                set @output= concat(@output,substring(@val1,@i,1))  
                set @i=@i-1
            end
    return @output
end
;