Thursday, July 11, 2013

Wildcards while Searching Text in SQL Continued

One part of using wildcards that wasn't covered yesterday was the ability to use the keyword NOT in conjunction with LIKE.  Using an example from yesterday, this returns all customers that start with the letter L:

SELECT *
FROM Customer
WHERE CompanyName LIKE 'L%'

If we put the word NOT before LIKE, we will get all customers that do not start with an L:

SELECT *
FROM Customer
WHERE CompanyName NOT LIKE 'L%'

Likewise, we can exclude any customer that has an L in their name:

SELECT *
FROM Customer
WHERE CompanyName NOT LIKE '%L%'

The same works with another one of our examples:

SELECT *
FROM Customer
WHERE FirstName LIKE '_ean'

The above returns all customers that have a name that is four digits and ends in "ean".  The following returns any customer not with four digits AND does not end with "ean".  It could still be four digits, but cannot end with "ean".

SELECT *
FROM Customer
WHERE FirstName NOT LIKE '_ean'

No comments:

Post a Comment