Which option is better to search in two tables in varchar columns?
Paul White wrote this article about using a trigram wildcard string search in place of the LIKE operator. It's quite an in-depth article, but it's probably the best you'll find on the subject (like a lot of what Paul White writes). I've implemented it in a test environment and it really is lightning fast compared to the LIKE operator.