To check a string contains a sub string or not we can use [LIKE] or [CHARINDEX].
I know that like uses indexing in case we have name%.(while %name% may not use index) .Also charindex does not use index.
For example I want to get all rows containing employeename as 'av'
select * from employee where CHARINDEX('av', employeename)>0
OR i can do same by using LIKE
select * from employee where employeename LIKE '%av%'
In this case which one is better to use? Charindex or like? Why?