>The LIKE will find the search value at the beginning of the text only and Contains - any word that matches criteria. It means that Contains is redundant in your case.
That's my point. This is why I found it weird that I made some code at one point that generated such a SQL. In regards to that particular form, we need to support a full text search on the field. So, the main SQL will have the Contains as is but I will remove the second part of the where clause.