SQL Issues: LIKE '%stuff%'

The SQL 'LIKE' operator is powerful. It allows us to do wildcard searches on column values. We love to use it, but sometimes we misuse it.

I often see developers use the LIKE operator to do a substring search. This works great, but performance is often poor.

SELECT Column1
  FROM Table1
 WHERE Column1 LIKE '%Stuff%'

When I see this in a code review, I usually ask if it's possible to remove the leading wildcard character since it will improve performance.

SELECT Column1
  FROM Table1
 WHERE Column1 LIKE 'Stuff%'

Obviously, we often require the leading and trailing wildcard, but it never hurts to ask.

But developers that are inexperienced with SQL don't always understand why the two clauses are so different. For the geeks that want to look it up, one query is sargable and the other is not. I usually explain it with this example.

Scan the list below and find all the rows that match LIKE '%Nu%'.

Now find the rows that match LIKE 'Nu%'.

Pellentesque cursus enim in viverra blandit.
Vestibulum vitae sem in magna egestas pellentesque ultrices nec sem.
Nulla pulvinar ipsum vel dolor imperdiet feugiat.
Proin et nibh fermentum, eleifend magna sed, imperdiet leo.
Praesent varius lorem eu sagittis rhoncus.
Nunc ornare massa vitae semper ullamcorper.

It was probably much faster for you to find the rows for the second query. The same is true for SQL. Basically, the sooner you can determine a row does not satisfy the criteria, the faster the search.