Issue Using SQL Replace Function with Variable

Question

Essentially, I'm trying to loop through the contents of @sprocs variable, implement the replace function, and print the respective changes. When I execute the code, the @spname variable is printed off, just not with the expected result.

declare @sprocs cursor
declare @spname nvarchar(max)

set @sprocs = cursor for
select ROUTINE_DEFINITION
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE = 'Procedure' AND
ROUTINE_DEFINITION like '%someString%'


open @sprocs
fetch next from @sprocs into @spname
while @@FETCH_STATUS = 0
Begin
    set @spname = replace(@spname, '%someString%', 'Hello')
    print @spname
    fetch next from @sprocs into @spname
end

Expected result would look like this:

Before

Lorem ipsum dolor sit amet, someString adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco someString nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. someString sint occaecat cupidatat non someString, sunt in culpa qui officia deserunt mollit anim id est laborum.

After

Lorem ipsum dolor sit amet, Hello adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco Hello nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Hello sint occaecat cupidatat non Hello, sunt in culpa qui officia deserunt mollit anim id est laborum.

I thought - initially - that is might be an issue with @spname variable type, but since it's declared as an nvarchar(max), I can't see that being the issue.

What's preventing the expected print out?

you might want to use replace(@spname, 'someString', 'Hello') instead of replace(@spname, '%someString%', 'Hello') .. the string to be replaced should be declared literally .. wildcards don't work here. — Vamsi Prabhala
@vpk, that worked. I clearly don't understand how wildcards work. I know you stated that it's because the string I'm looking for in this case was declared literally, but why wouldn't wildcards work in this situation? Wouldn't the wildcards indicate that it could be found within any part of the string? — Jade Cowan
@vpk, if you add your comment as an answer, I will gladly accept it. — Jade Cowan
@cwanjt You need the wildcards to define a search pattern. %a would mean ends with "a", while %a% means *"a" is somewhere in the middle and a% means "starts with "a". A REPLACE will find and replace the given string wherever it is located - even several times. If you'd know that the string to change is at the beginning or at the end, you'd probably use LEFT or SUBSTRING to cut this part off and concatenate the rest with your new string. — Shnugo
Do you really need a cursor for that? Why not just use a simple select? — Zohar Peled

iamdave iamdave · Accepted Answer · 2017-01-26T10:39:41

In addition to not using wildcards in your searchstring, you also really don't need a cursor for this. SQL works on the basis of data sets and applies anything you do in a standard select statement to all rows that are returned.

In light of this, your statement should just be:

select ROUTINE_DEFINITION
      ,replace(ROUTINE_DEFINITION,'someString','Hello') as ReplacedValues
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE = 'Procedure'
  and ROUTINE_DEFINITION like '%someString%'

which will apply the replace function to each and every value of ROUTINE_DEFINITION returned by the query.

Issue Using SQL Replace Function with Variable

1 Answers