Is there and alternative to LIKE statement in T-SQL?

Question

I have a scenario where I need to perform following operation:

SELECT *
FROM
[dbo].[MyTable]
WHERE
[Url] LIKE '%<some url>%';

I have to use two % (wildcard characters) at the beginning and the end of Url ('%<some url>%') as user should be able to search the complete url even if he types partial text. For example, if url is http://www.google.co.in and user types "goo", then the url must appear in search results. LIKE operator is causing performance issues. I need an alternative so that I can get rid of this statement and wildcards. In other words, I don't want to use LIKE statement in this scenario. I tried using T-SQL CONTAINS but it is not solving my problem. Is there any other alternative available than can perform pattern matching and provide me results quickly?

Answer 1

Starting a like with a % is going to cause a scan. No getting around it. It has to evaluate every value.

If you index the column it should be an index (rather than table) scan.

You don't have an alternative that will not cause a scan.
Charindex and patindex are alternatives but will still scan and not fix the performance issue.

Could you break the components out into a separate table?
www
google
co
in

And then search on like 'goo%'?
That would use an index as it does not start with %.

Better yet you could search on 'google' and get an index seek.

And you would want to have the string unique in that table with a separate join on Int PK so it does not return multiple www for instance.

Suspect FullText Contains was not faster because FullText kept the URL as one word.