SQL Server query to match an exact word and replace it with another string

I am using the following sql to replace all occurrences of one string with another.

Example: I want to update all occurrences of ‘test’ with ‘prod’.
I want to avoid matching strings like ‘test123’, ‘123test’, ‘testiest’…

This is just an example. These search and replace strings are configurable.

This is the sql that I am using:
String sql = "UPDATE BODYCONTENT SET BODY = CAST(REPLACE(CAST(BODY as nvarchar(max)), ? , ?) as ntext) WHERE BODY like '%[^a-z0-9]' + ? + '[^a-z0-9]%' OR" + " BODY like ? + '[^a-z0-9]%' OR" + " BODY like '%[^a-z0-9]' + ? OR" + " BODY like ?";

But here, it is matching 123test, test123, testest and updating it as 123prod, prod123 and prodprod. How can I avoid this behavior?

I am trying something similar with another database

select * from dbo.persons where LastName like '%[^a-z][^0-9]Dan[^a-z][^0-9]%' OR 
  LastName like 'Dan[^a-z][^0-9]%' OR 
  LastName like '%[^a-z][^0-9]Dan'

I could match all Dan and not Dan123 or 12Dan or Danville…
Please let me know your thoughts.

Since you are using regex and you want to replace words consider using\b metacharacter:
https://www.regular-expressions.info/wordboundaries.html
For example \bprod\b pattern will match only prod, not 123prod, neither prodprod, nor prod123.
I dont know how mysql treats though those

1 Like