Wildcards while Searching Text in SQL

Let's say you are looking for some text in SQL, but not exactly sure what the text is.  For example, you are looking for any customer where their last name starts with an L.  How would you do that in SQL?

There are two ways you can accomplish this.  The first is by using substring like the following:

SELECT *
FROM Customer
WHERE Substring(CompanyName, 1, 1) = 'L'

This would certainly work, but makes your search NonSARGable (to read up more on SARGable queries, go to this post Non-SARGable Queries)

Luckily, SQL has a logical operator called LIKE (which keeps your query SARGable), that allows you to search for text that matches a specific pattern, as follows:

SELECT *
FROM Customer
WHERE CompanyName like 'L%'

In this instance, the percent sign (%) allows for any characters before or after it, depending on where the % is placed.  The above would look for customers that started with an L.  The following would look for all customers that ended with an L:

SELECT *
FROM Customer
WHERE CompanyName like '%L'

This would look for all customers that had an L anywhere in its name:

SELECT *
FROM Customer
WHERE CompanyName like '%L%'

The % is a wild card option in SQL.  There are other wild card options to consider:

• _ (underscore)
• The underscore represents a single value.  For instance, if you are looking for a four letter name that ends in "ean", you could run the following:

SELECT *
FROM Customer

WHERE FirstName like '_ean'

• [ ]
• A single character within a range or given set.  For instance:

--Range of letters

SELECT *
FROM Customer

WHERE FirstName like '[D-S]ean'

--Set of letters

SELECT *
FROM Customer

WHERE FirstName like '[DEFGHS]ean'

• [^]
• A single character not within a range or give set

--Not in this range of letters

SELECT *
FROM Customer

WHERE FirstName like '[^D-S]ean'

--Not in this set of letters

SELECT *
FROM Customer

WHERE FirstName like '[^DEFGHS]ean'

To read more on LIKE and different wild card options, go here: LIKE

Non-SARGable Queries

As I've been doing some research online, I've come across a term that I am not familiar with.  This term is SARGable and is used in reference to the WHERE clause of select statements.  SARG actually stands for Search ARGument able.  Basically, if you have an index or multiple indexes on a table, a SARGable WHERE clause can take advantage of your indexes.  On the other hand, a non-SARGable WHERE clause cannot take full advantage of the indexes and will cause your query to take longer to run.

Keep in mind that this only applies to WHERE clauses.  If these statement are used in your SELECT statement, it will not affect performance.

So, what makes a query non-SARGable?  Here are some examples:

• Using LIKE with a leading %
• SELECT...... WHERE LastName like '%mith'
• Using a field within a function in the WHERE clause
• SELECT...... WHERE RIGHT(LastName, 5) like 'mith'

Each of these queries skips the index and uses a table scan, causing your query to run longer and resources to be tied up.  So what can we do instead?

• Use =, >, <, >=, <=, BETWEEN, LIKE without a leading %
• SELECT....... WHERE LastName like 'Smith%'
• Use IN, OR, NOT IN, NOT EXISTS, NOT LIKE
• SELECT....... WHERE LastName not in ('Smith')

Using these clues can increase your performance and should be used at all times when possible.

Wikipedia on "SARGable"