0

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?

IT researcher
  • 3,131
  • 15
  • 57
  • 82
  • Neither are sargable. I'd start by comparing the query plans, but I imagine you'll get a full index scan for both. – Mark Sinkinson Sep 15 '14 at 12:26
  • @MarkSinkinson Yes. both are not sargable. So which one would perform better in general? – IT researcher Sep 15 '14 at 12:30
  • My test suggests that the LIKE is "slightly" faster. Note that LIKE looks for existence, but CHARINDEX also reports on the string's position. – RLF Sep 15 '14 at 12:32

0 Answers0