5

I want to match the beginning of a string in a WHERE clause. While I recognise that so often database-performance is dependant on the underlying data structure, is there a best-practice for doing this? Is there one option which always outperforms the rest?

I want to avoid LIKE as I assume it will be less efficient than LEFT or CHARINDEX. As far as I can tell, my choices are below. Other suggestions welcome:

DECLARE @MyField as varchar(10)
SET @MyField = 'HELLOWORLD'
SELECT 1 WHERE @MyField LIKE 'HELLO%'
SELECT 2 WHERE LEFT(@MyField, 5) = 'HELLO'
SELECT 3 WHERE CHARINDEX('HELLO', @MyField) = 1

Kind Regards,

Jase.

1
  • 1
    WHERE column LIKE 'val%' will still be able to seek on an index (depending on the SELECT list and other filters, of course). Instead of assuming, why not test it? – Aaron Bertrand Aug 12 '13 at 15:04
7

The biggest factor that I can think of is that LEFT(MyField,5) and CHARINDEX('HELLO',@MyField) won't use an index unless the index matches the expression exactly.

However MyField LIKE 'HELLO%' will use any index with MyField in it.

4
  • So if I created an index on the column then I should get similar performance out of any of those options? – Bassmanjase Aug 12 '13 at 3:28
  • @Bassmanjase - No with an index LIKE can be better than the other two. As long as there is no leading wildcard it can perform an index range seek rather than scanning the whole thing. – Martin Smith Aug 12 '13 at 8:57
  • @Bassmanjase the only way a field with a function around it will use an index is if that index EXACTLY matches the function. So for LEFT(MyField,5) you would have to have an index on LEFT(MyField,5). So by preference any comparison you make you want to do the calculations on the side with the literal (assuming there is a literal of course). – Kenneth Fisher Aug 12 '13 at 11:47
  • Great, thanks for the info gents, LIKE with an index it is. :) – Bassmanjase Aug 12 '13 at 22:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.