Getting Strings to Work Like You Think They Would

When we start playing with strings, one of the first functions we work with is the “LIKE” operator. It’s fairly easy to grasp and easy to remember how to deal with.

Thus why we’re going to go over it today because there are various possibilities with it.

The first, and arguably, what you do most often when using the LIKE operator – search for a string within a string:

SELECT *
FROM Person.Person
WHERE LastName LIKE '%she%'

This pattern looks for the phrase “she” anywhere within the LastName field. It’s really useful when what you’re looking for is anywhere within the field, but you either don’t know or care about anything more than that.

Next is when we know that the substring we’re looking for is at the start of the larger string.

SELECT *
FROM Person.Person
WHERE LastName LIKE 'she%'

This pattern is very useful because it does use what is known as “table seeks” to find the data we’re looking for quickly.

Next, we look for the actual string itself. This occurs when you actually want the string to be equivalent to the larger string or when a developer mistakenly leaves the % off of the query.

SELECT *
FROM Person.Person
WHERE LastName LIKE 'she'

This query is functionally equivalent to WHERE LastName = ‘she’ so you truly get no performance boost or any additional responses from using the LIKE operator.

Finally, we should look for the substring that occurs at the end of the larger string.

SELECT *
FROM Person.Person
WHERE LastName LIKE '%she'

Now, this uses “table scans” – which are way less helpful than “table seeks” – because SQL Server has to physically go through each row looking to see if the last 3 characters are “she.”

To help SQL Server out a little bit, you can do the following:

SELECT *
FROM Person.Person
WHERE REVERSE(LastName) LIKE 'ehs%'

In this attempt, we are using the REVERSE function to “flip” all of the LastName fields so that the last character is first and the first character is last. Then we are looking for “ehs” – “she” in reverse – to see if the characters exist. Sadly, this does mean that you have to take on the added time cost of reversing every LastName field, which can be significant.

Remember that you can also use the PATINDEX or CHARINDEX operators to get this data!

With that, you have several ways to find a string within a string. I leave it up to the reader to find ways that work for you and your data – remember that it may work better one way one time and another way another time. Try the different ways of working with strings and see what gets the fastest results!

With that, I leave you until next time my friends. Go out and play with strings finding what works, what you have questions on, what just doesn’t make sense. Until then my friends!

Keep on searchin’!