Is there any way to loop a string in SQL Server?

Question

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

"F1 100 F2 400 F3 600"

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

Why do you want to loop in SQL Server at all? SQL Server is a set based language; the *last* thing you should be doing is looping. — Thom A, Jun 07 '21 at 09:23
probably you can split your string, put splitted values into table... and then try to divide each rowvalue by 10... https://stackoverflow.com/questions/19885076/how-to-split-string-and-insert-values-into-table-in-sql-server/46695204 as example how to split — demo, Jun 07 '21 at 09:29
@Larnu, that's why I'm asking, to find the best solution, I know I shouldn't do a loop........ — Noelia, Jun 07 '21 at 09:38

Thom A · Accepted Answer · 2021-06-07T10:07:53.417

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.

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.

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

And call is as such:

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

db<>fiddle

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.

Is there any way to loop a string in SQL Server?

1 Answers1

Linked