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