有没有办法在 SQL Server 中循环字符串? [英] Is there any way to loop a string in SQL Server?

问题描述

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

I am trying to loop a varchar in SQL Server, one of the columns has the format

"F1 100 F2 400 F3 600"

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

What I need is to take the numbers and divide by 10: "F1 10 F2 40 F3 60", for the moment I have a stored procedure which calls this function:

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 

推荐答案

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

We seem to have 2 problems here. Firstly the fact that you want to loop in SQL, however, SQL is a set based language. This means that it performs great at set-based operations but poorly at iterative ones, such as a loop.

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

Next is that you have what appears to be delimited data, and that you want to affect that delimited data in some way, and the reconstruct the data into a delimited string. Storing delimited data in a database is always a design flaw, and you should really be fixing said design.

因此，我建议您改用内联表值函数而不是标量函数.

I would therefore propose you move to an inline table-value function over a scalar function.

首先，由于值的序数位置似乎很重要，我们不能使用 SQL Server 内置的 STRING_SPLIT，因为它被记录为不能保证值的顺序将相同.因此，我将使用 DelimitedSplit8K_LEAD 给出序号位置.

Firstly, as it appears that the ordinal position of the values is important we can't use SQL Server's built in STRING_SPLIT, as it is documented to not guarantee the order of the values will be the same. I am therefore going to use DelimitedSplit8K_LEAD which gives the ordinal position.

然后我们可以使用TRY_CONVERT来检查该值是否为int(我假设这是正确的数据类型)，以及是否除以<代码>10.最后我们可以使用 STRING_AGG 重构数据.

Then we can use TRY_CONVERT to check to see if the value is an int (I assume this is the correct data type), and if it is divide by 10. Finally we can reconstruct the data using STRING_AGG.

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

Outside of a function this would look like this:

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;

作为一个函数，你可以这样做:

As a function, you could therefore do this:

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<>fiddle

请注意，STRING_AGG 是在 SQL Server 2017 中引入的；如果您使用的是旧版本(您没有注意到这是问题)，则需要使用旧"版本.FOR XML PATH 解决方案，显示在此处.

Note that STRING_AGG was introduced in SQL Server 2017; if you're using an older version (you don't note this is the question) you'll need to use the "old" FOR XML PATH solution, shown here.

这篇关于有没有办法在 SQL Server 中循环字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！