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.