Is it possible to have indexes for like '%*%' type queries
For LIKE queries this is not available.

But for making such queries SQL Server provides functionality called Full-text search. More info in MSDN.