代码之家 › 专栏 › 技术社区 › Chan Yoong Hon

SQL函数:在MSSQL Server 2014中修改字符串并返回

sql-server-2014 function sql-server sql

Chan Yoong Hon · 技术社区 · 8 年前

我是sql函数的新手,我有string@string=“233344555”。如何修改字符串并将其返回为“A_,B_,C_”?

在函数中,必须用“,”字符分割字符串。在第一个数字中,我必须加上第一个数字,在第二个数字中我必须加上第二个号码,再加上第三个号码,以此类推。

接下来,在我的函数中,我还必须将它作为字符串返回。比如“A_,B_,C_”。

如何在MSSQL函数中执行此操作?

4 回复 | 直到 8 年前

Shakeer Mirza 8 年前

另一个简单的方法是

DECLARE @string VARCHAR(MAX)= '233, 344, 555', @STRING2 VARCHAR(MAX)='';

SELECT @STRING = '<M>'+REPLACE(@STRING,', ','</M><M>')+'</M>';

SELECT @STRING2 = @STRING2+',' +NEW FROM (
SELECT char( row_number() over(order by(select 1))+64)+'_'+
SPLT.X.value('.','varchar(10)')  AS NEW
FROM(
SELECT CAST(@STRING AS XML) SXML
)A 
CROSS APPLY 
  SXML.nodes('/M') as SPLT(X)
)BB

SELECT SUBSTRING(@STRING2,2,LEN(@STRING2))

结果:

 A_233,B_344,C_555

Yogesh Sharma 8 年前

你可以用 value 建造并完成 JOIN :

select concat(t.chars, '_', t1.function_out) as numbers
from ( values (1, 'a'), (2, 'b'), (3, 'c') . . . ) t(id, chars) inner join 
           (select row_number() over (order by (select 1)) as id, 
                   function_out 
            from <your split function here>(@string, ',')
           ) t1 on t1.id = t.id;

7schmiede 8 年前

DECLARE @string NVARCHAR(MAX) = '233, 344, 555';

DECLARE @tParts TABLE (
    id INT IDENTITY (1, 1),
    part NVARCHAR(10)
);

-- Split your String with a Split-Function like

--INSERT INTO @tParts
--SELECT item 
--FROM dbo.Split(@string, ',');

-- Or use Demo-Values
INSERT INTO @tParts 
VALUES (N'1'), (N'2'), (N'3') -- ...

DECLARE @tPrefixes TABLE (
    id INT IDENTITY (1, 1),
    prefix NVARCHAR(10)
);

INSERT INTO @tPrefixes 
VALUES (N'a'), (N'b'), (N'c') -- ...

DECLARE @output NVARCHAR(MAX) = N'';

SELECT @output += pr.prefix + N'_' + ps.part + N', '
FROM @tPrefixes AS pr
    INNER JOIN @tParts AS ps
        ON ps.id = pr.id;

-- Remove last N', '
SET @output = LEFT(@output, LEN(@output)-1);

SELECT @output AS [output];

cloudsafe 8 年前

使用XML更改为行并返回:

alter function dbo.ModifyString(@input Varchar(max), @Splitter VarChar(99)) returns nvarchar(max)
as
begin
declare @str as nvarchar(max)
;with cte as ( SELECT  ltrim(Split.a.value('.', 'VARCHAR(max)')) AS Data 
    FROM
    ( SELECT CAST ('<M>' + REPLACE(@input, @Splitter, '</M><M>') + '</M>' AS XML) AS Data 
    ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a))
 select @str = stuff((
        select ', ' + char(row_number() over (order by [data]) + 96) + '_' + [Data]
        from cte x
        order by x.Data
        for xml path('')
    ),1,2,'') 
from cte x;
return @str
end

declare @string as nvarchar(max) = '233, 344, 555'

select dbo.ModifyString(@string, ',' )