时间戳

首页 > 解决方案 > 有没有办法在 SQL Server 中循环字符串？

sql - 有没有办法在 SQL Server 中循环字符串？

问题描述

我正在尝试在 SQL Server 中循环一个 varchar，其中一列的格式为

"F1 100 F2 400 F3 600"

我需要的是取数字并除以 10：“F1 10 F2 40 F3 60”，目前我有一个调用此函数的存储过程：

ALTER FUNCTION [name_offunction]
    (@Chain varchar(120)) 
RETURNS varchar(120
AS
BEGIN
    DECLARE @Result varchar(120), @Pos int, @Concat varchar(120)

    WHILE LEN(@Chain) > 0
    BEGIN
         SET @Pos = CHARINDEX(' ', @Chain)
         SET @Result = CASE
                           WHEN SUBSTRING(@Chain, 1, @Pos-1) LIKE '%[^A-Z]%' 
                                 THEN SUBSTRING(@Chain, 1, @Pos-1)
                           WHEN SUBSTRING(@Chain, 1, @Pos-1) NOT LIKE '%[^A-Z]%' 
                                 THEN CAST(CAST(SUBSTRING(@Chain, 1, @Pos-1) / 10 AS INT)AS CHAR)
                       END
         SET @Chain = REPLACE(@Chain, SUBSTRING(@Chain, 1, @Pos), '')
         SET @Concat += @Result + ' '
   END
   RETURN @Concat

标签： sqlsql-serverloopsselectstored-procedures

解决方案

我们这里似乎有两个问题。首先，您想在 SQL 中循环，但是，SQL 是一种基于集合的语言。这意味着它在基于集合的操作中表现出色，但在迭代操作中表现不佳，例如循环。

接下来是您拥有看似分隔的数据，并且您希望以某种方式影响该分隔数据，并将数据重建为分隔字符串。在数据库中存储分隔数据始终是一个设计缺陷，您真的应该修复上述设计。

因此，我建议您通过标量函数转向内联表值函数。

首先，看起来值的顺序位置很重要，我们不能使用 SQL Server 内置的STRING_SPLIT，因为据记载不能保证值的顺序是相同的。因此，我将使用DelimitedSplit8K_LEADwhich 给出序数位置。

然后我们可以TRY_CONVERT用来检查该值是否为 an int（我假设这是正确的数据类型），以及是否除以10. 最后，我们可以使用 重建数据STRING_AGG。

在函数之外，这看起来像这样：

DECLARE @Chain varchar(120) = 'F1 100 F2 400 F3 600';

SELECT STRING_AGG(COALESCE(CONVERT(varchar(10),TRY_CONVERT(int,DS.item)/10),DS.item),' ') WITHIN GROUP (ORDER BY DS.Item)
FROM dbo.DelimitedSplit8K_LEAD(@Chain,' ') DS;

因此，作为一个函数，您可以这样做：

CREATE FUNCTION dbo.YourFunction (@Chain varchar(120))
RETURNS TABLE AS
RETURN
    SELECT STRING_AGG(COALESCE(CONVERT(varchar(10),TRY_CONVERT(int,DS.item)/10),DS.item),' ') WITHIN GROUP (ORDER BY DS.Item) AS NewChain
    FROM dbo.DelimitedSplit8K_LEAD(@Chain,' ') DS;
GO

呼叫是这样的：

SELECT YF.NewChain
FROM dbo.YourTable YT
     CROSS APPLY dbo.YourFunction (YT.Chain) YF;

db<>小提琴

请注意，这STRING_AGG是在 SQL Server 2017 中引入的；如果您使用的是旧版本（您没有注意到这是问题），您需要使用“旧”解决方案，FOR XML PATH如图所示。

推荐阅读