Sql server parameterized query to match exact word

Question

I am using the following sql: String sql = "UPDATE BODYCONTENT SET BODY = CAST(REPLACE(CAST(BODY as nvarchar(max)), ? , ?) as ntext)"; PreparedStatement preparedstmt = conn.prepareStatement(sql); preparedstmt.setString(1, tom); preparedstmt.setString(2, jack); I want to match the exact word. like '% word %' example: I want to match only Tom , not Tommy, tomus, tomb or tom$ or tom123 Please help.

Alexey Matveev · Answer

I guess, It will be like this

[MyColumn] Like '% test %'

OR [MyColumn] Like '% test'

OR [MyColumn] Like 'test %'

OR [MyColumn] = 'test'

OR

[MyColumn] Like '% prod %'

OR [MyColumn] Like '% prod'

OR [MyColumn] Like 'prod %'

OR [MyColumn] = 'prod'

ilakkuvaselvi manoharan · Answer

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.

Alexey Matveev · Answer

Hello,

You can find an answer here:

https://stackoverflow.com/questions/6283767/match-only-entire-words-with-like