Q: Partial Searches in Sql Server? what is the best way to do search in a sql database, my website I am building will  be doing lots of user driven searches.
I first thought about using sql server full text search but now I am finding it seems to not support partial matches
Say I got this data.
Procter & Gamble
Google
Stackoverflow 
A&M Records

and I want to try to find anything with an & in it
SELECT * 
FROM dbo.[Companies]
WHERE CONTAINS(Name,'&')

select * FROM [Companies]
WHERE FREETEXT(Name,'&') 

both of these do not return any results. I would have to use
select * FROM [Companies]
WHERE Name like '%&%'

but from my understanding is like does a full table scan that can be slow. So I am not sure how this is going to effect my site speed when I am search through around 5 million records.
I know there is elastic search but I am afraid that is going to take me too long to learn and I am on a tight deadline so I need something that can work for the interim and then I can strive towards something better.
Edit
I should add another use case that I need to handle is something like a product (think of a TV what has almost the same model number but some letters at the end) number like
Product A - 123456
Product B - 123456A
Product C - 123456B

So basically if someone types in "123456A" then all 3 results should come back and this is something I am not sure if "like" can do. 
