SQL Server: Variable modified within WHILE loop appears unchanged when selected outside of loop

Question

I am seeing some strange behaviour when combining while loops with assignments in SQL Server. I have the following SQL Server TSQL code:

DECLARE @string nvarchar(max) = 'Foo bar bat.';
WHILE PATINDEX('bar', @string) != 0
BEGIN
    SET @string = REPLACE(@string, 'bar', '')
END
SELECT @string

I would expect the result here to be Foo bat., but the result is actually Foo bar bat., as if the original @string variable was unchanged. It doesn't get into an infinite loop though, which suggests that the value of @string did get updated in the way I wanted, but for some reason this gets forgotten when we break out of the while loop.

What exactly is causing this to happen, and how can I achieve the expected result?

I am running SQL Server 2016 SP2.

Why are you looping here anyway? A single call to your replace line would be sufficient. The loop will only ever run a single time. — Sean Lange
– Sean Lange, Commented Dec 4, 2019 at 16:55
This is a minimal example to demonstrate the problem, my actual code loops through a few times replacing different strings based on a variable that changes from iteration to iteration. — Rob Streeting
– Rob Streeting, Commented Dec 4, 2019 at 17:00
Not sure what you mean. Your code is taking a single value "bar" and looping over a value until it replaces all occurrences of that value which would be completed after exactly one iteration. Maybe your example is just simplified with a hard coded value? That would make sense. But you can actually simplify that too without loops. — Sean Lange
– Sean Lange, Commented Dec 4, 2019 at 17:01
In my actual code the string used in PATINDEX uses wildcards to match a generic pattern (consider something like _a_ in this example, that would match both 'bar' and 'bat'), find the first substring matching the pattern (e.g. 'bar'), do some processing and then REPLACE the exact substring out of the string, so that on the next iteration the 2nd substring matching the pattern (e.g. 'bat') gets matched and I can process that. Sorry that wasn't clear, I reduced the code down to a minimal example that gave the same error but maybe I should have left more of that logic in. — Rob Streeting
– Rob Streeting, Commented Dec 4, 2019 at 17:09
No worries mate. It just seemed very odd to have a loop here when it wasn't needed. Seems you found the reason for the issue which is the important thing. :D — Sean Lange
– Sean Lange, Commented Dec 4, 2019 at 17:11

Zohar Peled · Accepted Answer · 2019-12-04 16:35:06Z

2

Your loop is not running at all.

PATINDEX('bar', @string) returns 0. You need to add wildcards before and after bar. Change it to PATINDEX('%bar%', @string)

DECLARE @string nvarchar(max) = 'Foo bar bat.';

WHILE PATINDEX('%bar%', @string) != 0
BEGIN
    SET @string = REPLACE(@string, 'bar', '')
END
SELECT @string

Results:

Foo  bat.

answered Dec 4, 2019 at 16:35

Zohar Peled

82.9k11 gold badges80 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sean Lange Over a year ago

More importantly...what is the point of a loop here at all? A simple replace statement would accomplish the same thing since they are replacing every occurrence of "bar" with "bat".

EzLo · Accepted Answer · 2019-12-04 16:35:22Z

1

You need to use patterns on the search expression with PATINDEX.

pattern

Is a character expression that contains the sequence to be found. Wildcard characters can be used; however, the % character must come before and follow pattern (except when you search for first or last characters). pattern is an expression of the character string data type category. pattern is limited to 8000 characters.

So try with

DECLARE @string nvarchar(max) = 'Foo bar bat.';
WHILE PATINDEX('%bar%', @string) != 0
BEGIN
    SET @string = REPLACE(@string, 'bar', '')
END
SELECT @string

answered Dec 4, 2019 at 16:35

EzLo

14.2k10 gold badges38 silver badges40 bronze badges

Collectives™ on Stack Overflow

SQL Server: Variable modified within WHILE loop appears unchanged when selected outside of loop

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related