SQL LIKE Wildcard

Generally, we use the SQL Server LIKE Operator in the WHERE clause to perform wildcard search operations. The server checks and extracts the records whose values match the specified pattern. Here, we can use either the regular character or the available wildcards. When we perform the regular matching using the comparison operator (=), both sides must exactly match. However, the SQL LIKE operator with wildcards performs fuzzy search (substring), or marginal regular expressions. For example, if you forget the Spelling of a Company or Product, you can use the operator and wildcard to retrieve the required information.

The SQL Server LIKE operator is case-insensitive, so you don’t have to worry about the case of the search pattern. For instance, if the search pattern word is ‘USA’, it will return results including USA, Usa, UsA, usa, and Usa. We can use this operator on strings (recommended), numbers, and dates. Although this article mostly focuses on the SELECT statement, we can use the LIKE operator in UPDATE and DELETE statements.

SQL LIKE Wildcard Syntax

The basic syntax of the LIKE operator with a wildcard to perform search operations on table columns is as shown below.

SELECT column1, column2, ….
FROM table
WHERE column_name LIKE pattern

From the above SQL LIKE operator syntax:

  • column1, column2, ….: These are the list of columns that you want to retrieve from a table(s).
  • Table: It may be a single or multiple-source table from which we select the above-listed columns.
  • column_name: Specify the table column on which the LIKE operator should perform the search operation.
  • Pattern: Here, we must specify the search pattern. It includes placing a letter or word followed or preceded by available wildcards. The server will search the column_name for this pattern and return the matching records.

The next SQL LIKE operator section covers all available wildcards that can be used in real-time operations. In order to combine multiple LIKE operators, we must use Logical operators.

WHERE column1 LIKE 'S%' AND/OR column2 LIKE '[a-s]%'

Apart from the SELECT statement, we can utilize the LIKE operator with wildcards in the UPDATE and DELETE statements.

UPDATE table
SET column = ValueN
WHERE column_name LIKE pattern

The DELETE statement with the LIKE operator.

DELETE FROM table
WHERE column_name LIKE pattern

NOTE: There is a NOT LIKE operator that acts exactly opposite to the above, and we will discuss it later on this page.

SQL Wildcard Examples

As we mentioned earlier, wildcards will help replace the missing characters or words. If you don’t know a character or a substring, substitute that unknown phrase with a wildcard, and the server will pick the best combinations. To utilize or work with the LIKE operator, we must understand the wildcards and their behaviour.

The SQL Server LIKE operator supports the following wildcards. As you can see, there are multiple wildcards, and among them, % and _ are the most commonly used ones.

WildcardsDescription
%It represents zero or more characters.
_It represents exactly one character.
[]Character list. It selects the records that exactly match the individual characters within that list (square brackets). [ABC]% means anything that starts with either A, B, or C.
[^]Selects the records that do not match the characters present in the square brackets. [^UP]% mean excludes the names that start with U or P.
[-]Select the records that exactly match the range of characters. The range starts from the character mentioned before – symbol, and ends with a character mentioned after -. [A-C]% means names start with A, B, and C.
[^-]It does exactly the opposite of the above [-]. [^A-C]% means exclude the names that start with A, B, and C in the result set.

You can also use the SQL LIKE operator with wildcard characters (% and _) as literal characters. However, you have to do some extra work to achieve this. Please check the ESCAPE sequence section to understand the approach.

Let us see how to use wildcards along with the SQL Server LIKE operator with examples. For this demonstration, we use the table data that we mentioned below.

ProductIDProductNameCategoryPriceCountryCity
1Apple iPhone 17Electronics899.00USACupertino
2Samsung Galaxy S25Electronics899.00South KoreaSuwon
3Apple MacBook Air LaptopElectronics1199.00USACupertino
4Dell Inspiron 14 LaptopElectronics1854.00USARound Rock
5Adidas Atmosphere Blue AirFootwear120.00GermanyHerzogenaurach
6Apple Watch Series 9Electronics499.00USACupertino
7Lenovo Yoga Slim 9i LaptopElectronics2000.00ChinaBeijing
8Air Jordan 1 HighFootwear250.00USABeaverton
9Puma T-shirtClothing65.00GermanyHerzogenaurach
10SJYP JeansClothing399.00South KoreaSeoul
11Samsung Family Hub RefrigeratorHome Appliances399.00South KoreaSuwon
12Xiaomi Air Purifier H13 HEPA FilterHome Appliances100.00ChinaBeijing

The % Wildcard

When using the SQL Server LIKE operator, the percentage sign (%) wildcard is the most common and is utilized to perform a search. The percentage (%) wildcard matches any number of characters (zero or more characters). Suppose you want to substitute a series of characters (any string length); use the percentage symbol.

The best part is that we can use this % wildcard before, after, and in the middle of characters. For example, %sale% means find all records containing sale at any position (first, second, middle, last, and so on).

Starts with

As the % is the most important wildcard in SQL LIKE operator, we will provide multiple examples. If you use the % symbol at the end, it returns all records that start with that letter. Suppose you know the starting word of any product, or the letter of a person, use that letter or word, followed by a % wildcard, to retrieve the information.

In the following query, we used the LIKE operator with Apple%. It means any ProductName that starts with Apple and may contain zero or more characters. So, the query selects all Apple products in a table.

SELECT * FROM Products
WHERE ProductName LIKE 'Apple%';

From the table below, you can see all Apple products. If you have any product with the name Apple, the result set will also return that product.

Similarly, the query below prints all city names starting with the letter B.

SELECT * FROM Products
WHERE City LIKE 'B%';

If you want to use the same example with the AdventureWorks database, use the following query to find all customers whose last name starts with ‘W’.

SELECT * FROM DimCustomer
WHERE LastName LIKE 'W%'

Ends With

If we use the % wildcard at the beginning, followed by a letter or phrase, the SQL LIKE operator query returns all the records that end with the specified letter or phrase. For instance, if you know the letter word of the product description, or a character in a city, or a customer name, use the % at the beginning.

The following query uses the % symbol, followed by the word ‘Laptop’. It means the query will return all products whose product name may start with N number of characters, but it should end with Laptop.

SELECT * FROM Products
WHERE ProductName LIKE '%Laptop';

Similarly, the query below returns all products whose City name must end with “on” (Suwon and Beaverton).

SELECT * FROM Products
WHERE City LIKE '%on';

The SQL Server LIKE operator also allows you to combine the “Start with” and “End with”. The query below returns all products manufactured in the city name that starts with “S” and ends with “l”.

SELECT * FROM Products
WHERE City LIKE 'S%l';

Contains

As our table is pre-designed according to needs, the product names are well organized. For example, we added the Laptop at the end of Apple, Lenovo, and Dell products. However, in real-time, it may not be the case because the word Laptop may be present at any place. To deal with it, we must use the LIKE operator and place the percentage (%) symbol on both sides (before and after) of the word Laptop.

For instance, the following SQL Server LIKE operator query returns all products where the Product name contains “Air” at any position (starting, middle, end, or N position).

SELECT * FROM Products
WHERE ProductName LIKE '%Air%';
SQL LIKE Operator with Wildcards Example

The _ wildcard in SQL LIKE operator

The Underscore sign (_) represents a single character, and it can be a number or a character. It informs the server to look for one character in the sequence either before or after the character. At first glance, the _ wildcard may look useless compared to the %. However, it has its own advantages, and in this page, we will cover them. For example, finding the possible combinations of numbers, such as printing all phone numbers (550-555-01__) starting with 550-555-01 and using the last two unique combinations.

The following SQL query uses the LIKE operator with a _ wildcard to find all products manufactured in a country whose name starts with U, one missing letter, and ends with A.

As we all know, _ represents a single character; the country name should be a three-letter word. It may be the USA or UGA. As our table has only the USA information, it prints those records.

SELECT * FROM Products
WHERE Country LIKE 'U_A'
ProductID	ProductName	Category	Price	Country	City
1	Apple iPhone 17	Electronics	899.00	USA	Cupertino
3	Apple MacBook Air Laptop	Electronics	1199.00	USA	Cupertino
4	Dell Inspiron 14 Laptop	Electronics	1854.00	USA	Round Rock
6	Apple Watch Series 9	Electronics	499.00	USA	Cupertino
8	Air Jordan 1 High	Footwear	250.00	USA	Beaverton

Similarly, use the query below to find products from Beijing. If you are unfamiliar with spellings, try this approach.

SELECT * FROM Products
WHERE City LIKE 'B_ij__g'
ProductID	ProductName	Category	Price	Country	City
7	Lenovo Yoga Slim 9i Laptop	Electronics	2000.00	China	Beijing
12	Xiaomi Air Purifier H13 HEPA Filter	Home Appliances	100.00	China	Beijing

When using the _ wildcard, it is not mandatory to use at least one character along with the wildcard; we can ignore all characters. If you don’t know the missing characters or want to display all the customers or employees whose name has exactly five characters, place five empty _ symbols.

The SQL LIKE operator query below prints all products manufactured in the country whose name has exactly 11 letters. It includes spaces because space is just a character.

SELECT * FROM Products
WHERE Country LIKE '___________'
ProductID	ProductName	Category	Price	Country	City
2	Samsung Galaxy S25	Electronics	899.00	South Korea	Suwon
10	SJYP Jeans	Clothing	399.00	South Korea	Seoul
11	Samsung Family Hub Refrigerator	Home Appliances	399.00	South Korea	Suwon

The above query can also be returned as follows. Here, instead of the LIKE operator, we use the LEN() function to show the countries with a length of 11.

SELECT * FROM Products
WHERE LEN(Country) = 11

Apart from the characters, we can use the _ wildcard on numbers. The query below returns all products with a three-digit price, and the ending number should be 9. In this case, it will return products with price tags of 399, 499, and 899.

SELECT * FROM Products
WHERE Price LIKE '__9.00'
ProductID	ProductName	Category	Price	Country	City
1	Apple iPhone 17	Electronics	899.00	USA	Cupertino
2	Samsung Galaxy S25	Electronics	899.00	South Korea	Suwon
6	Apple Watch Series 9	Electronics	499.00	USA	Cupertino
10	SJYP Jeans	Clothing	399.00	South Korea	Seoul
11	Samsung Family Hub Refrigerator	Home Appliances	399.00	South Korea	Suwonc

SQL LIKE operator with the [] wildcard

The [] (character list) wildcard in the SQL LIKE operator allows passing a list of characters, and if any of them match, it returns that record. The [] wildcard helps identify or search for similar names (text) or phrases with slight differences (may be a letter). It is hard to understand the definition, but a simple example helps.

The query below returns all customers whose last name starts with ‘S’ and ends with ‘n’. The middle letter must be any of the mentioned (aeouk) combinations. It may be Son, Sun, Sen, San, and Skn.

SELECT * FROM customers
WHERE LastName LIKE 'S[aeouk]n'

We can use the [] wildcard at the start, end, or at any given position. Remember, position is significant. For instance, the following query returns the customers whose first name starts with any of the letters (c, h, p, r, and s) followed by ‘am’. They are Cam, Ham, Pam, Ram, and Sam. Similarly, we can use it at the end.

SELECT * FROM customers
WHERE FirstName LIKE '[chprs]am'

[-] wildcard

When we place a – inside the [] wildcard, it looks for a range of sequential alphabetical characters. For example, [A-D] means names start with A, B, C, and D.

The SQL LIKE operator query below returns all customers whose last name starts with ‘s’, followed by any alphabet from a to ‘k’, and ends with n. Here, a to k [a-k] means a, b, c, d, e, f, g, h, I, j, and k. It includes san, sbn, scn, sdn, (if any), sen, sfn, sgn, shn, sin, sjn, and skn.

SELECT * FROM customers
WHERE LastName LIKE 'S[a-h]n'

Using the [^] wildcard

In SQL Server, the LIKE operator with the [^] wildcard acts exactly opposite to the []. Basically, ^ is a NOT operator, and it excludes the list mentioned within the [].

The query below uses [^clst] at the starting position, followed by the word ‘ara’. It means the result set should not include FirstName that starts with letters c, l, s, or t. For example, the result set won’t return names like Cara, Lara, Sara, Tara, and so on. However, it will display the FirstName of Mara, Kara, etc.

SELECT * FROM customers
WHERE FirstName LIKE '[^clst]ara'

Combine Wildcards in the SQL LIKE operator

In this section, we will combine multiple wildcards within a single LIKE operator statement to perform wide search operations.

Combine % and _

When we use the _ wildcard, it may look unnecessary. However, when we combine this _ wildcard with the % symbol, it performs complex search operations.

The SQL LIKE operator query below returns products manufactured in the city whose name has ‘e’ at the second position. It means there should be a first letter (any), followed by ‘e’, and then followed by N number of characters. They are Beijing, Beaverton, Herzogenaurach, and Seoul.

SELECT * FROM Products
WHERE City LIKE '_e%'
ProductID	ProductName	Category	Price	Country	City
5	Adidas Atmosphere Blue Air	Footwear	120.00	Germany	Herzogenaurach
7	Lenovo Yoga Slim 9i Laptop	Electronics	2000.00	China	Beijing
8	Air Jordan 1 High	Footwear	250.00	USA	Beaverton
9	Puma T-shirt	Clothing	65.00	Germany	Herzogenaurach
10	SJYP Jeans	Clothing	399.00	South Korea	Seoul
12	Xiaomi Air Purifier H13 HEPA Filter	Home Appliances	100.00	China	Beijing

We can apply the same principle to the numbers as well. The query below returns products with a price tag where the second digit should be 5, followed by one or more digits. It may be 25, 15, 45, 659, etc, and in this case, 250.00, and 65.00.

SELECT * FROM Products
WHERE Price LIKE '_5%'
ProductID	ProductName	Category	Price	Country	City
8	Air Jordan 1 High	Footwear	250.00	USA	Beaverton
9	Puma T-shirt	Clothing	65.00	Germany	Herzogenaurach

Combine % and []

By combining the [] and % wildcards in the SQL LIKE operator, we can perform a search for multiple items using the character list. The [] wildcard allows an exclusive list of items, and % allows 0 or more characters.

  • [abc]% = starts with a, b, or c.
  • %[abc] = ends with a, b, c.
  • _o[and] = Exactly a three letter word where the second alphabet is o and the third letter is a, n, or d.

The query below returns all products whose names start with either the letter L or X, followed by zero or more letters. For example, display Lenovo and Xiaomi products.

SELECT * FROM Products
WHERE ProductName LIKE '[LX]%'
ProductID	ProductName	Category	Price	Country	City
7	Lenovo Yoga Slim 9i Laptop	Electronics	2000.00	China	Beijing
12	Xiaomi Air Purifier H13 HEPA Filter	Home Appliances	100.00	China	Beijing

Combine [-] and %

When searching for a single character, a – wildcard may work. However, to perform a vast search, we must combine the SQL LIKE operator with – and a % wildcard. The query below returns products manufactured in countries starting with C, E, F, and G, followed by zero or more characters.

SELECT * FROM Products
WHERE Country LIKE '[C-G]%'
ProductID	ProductName	Category	Price	Country	City
5	Adidas Atmosphere Blue Air	Footwear	120.00	Germany	Herzogenaurach
7	Lenovo Yoga Slim 9i Laptop	Electronics	2000.00	China	Beijing
9	Puma T-shirt	Clothing	65.00	Germany	Herzogenaurach
12	Xiaomi Air Purifier H13 HEPA Filter	Home Appliances	100.00	China	Beijing

Combine [^], -, and %

In this SQL LIKE operator example, we will combine [], ^, -, and % wildcards. Here, [^E-G]% means categories do not start with E, F, and G, followed by a single or multiple characters.

SELECT * FROM Products
WHERE Category LIKE '[^E-G]%'
ProductID	ProductName	Category	Price	Country	City
9	Puma T-shirt	Clothing	65.00	Germany	Herzogenaurach
10	SJYP Jeans	Clothing	399.00	South Korea	Seoul
11	Samsung Family Hub Refrigerator	Home Appliances	399.00	South Korea	Suwon
12	Xiaomi Air Purifier H13 HEPA Filter	Home Appliances	100.00	China	Beijing

Combine [^] and %

Here, we will combine the [^] and % wildcard to exclude a list of items from the result set. The query below returns all products whose names do not start with either the letter A or S, followed by zero or more letters. For example, exclude products from Apple, Samsung, etc.

SELECT * FROM Products
WHERE ProductName LIKE '[^AS]%'
ProductID	ProductName	Category	Price	Country	City
4	Dell Inspiron 14 Laptop	Electronics	1854.00	USA	Round Rock
7	Lenovo Yoga Slim 9i Laptop	Electronics	2000.00	China	Beijing
9	Puma T-shirt	Clothing	65.00	Germany	Herzogenaurach
12	Xiaomi Air Purifier H13 HEPA Filter	Home Appliances	100.00	China	Beijing

Using LIKE Operator with AND/OR Example

The SQL Server AND and OR operators help combine two LIKE operators with distinct or the same wildcards. It helps to perform more complex operations or large search operations.

The following query uses the AND operator to combine two LIKE operators with a % wildcard. It prints all products manufactured in South Korea, and the city name should start with “Su”. In South Korea, there are two cities named Suwon and Seoul, and the following code returns Suwon.

SELECT * FROM Products
WHERE Country LIKE '%Korea' AND City LIKE 'Su%';
ProductID	ProductName	Category	Price	Country	City
2	Samsung Galaxy S25	Electronics	899.00	South Korea	Suwon
11	Samsung Family Hub Refrigerator	Home Appliances	399.00	South Korea	Suwon

The query below uses the OR operator to combine multiple LIKE operators. It prints all products manufactured in the country, starting with Ch (China). It also prints products belonging to the category name starting with Home (Home Appliances).

SELECT * FROM Products
WHERE Country LIKE 'Ch%' OR Category LIKE 'Home%';
ProductID	ProductName	Category	Price	Country	City
7	Lenovo Yoga Slim 9i Laptop	Electronics	2000.00	China	Beijing
11	Samsung Family Hub Refrigerator	Home Appliances	399.00	South Korea	Suwon
12	Xiaomi Air Purifier H13 HEPA Filter	Home Appliances	100.00	China	Beijing

Similarly, we can combine LIKE and NOT LIKE operators within the same query. The following example returns all products manufactured in countries whose names start with U (USA), and their category does not start with E (Electronics).

SELECT * FROM Products
WHERE Country LIKE 'U%' AND Category NOT LIKE 'E%';
ProductID	ProductName	Category	Price	Country	City
8	Air Jordan 1 High	Footwear	250.00	USA	Beaverton

Escape Characters in SQL LIKE Operator

When working with real-time, there are possibilities that the data may contain %, _, or even [] inside the data. For example, the sales percentage column contains 100%, 50%, or the name may contain John_Miller. To deal with these situations, we must use the escape character.

The escape character excludes the mentioned wildcard character from the expression and considers that wildcard as a normal character. The syntax of the SQL LIKE operator with the ESCAPE character is shown below.

SELECT column1, column2, ….
FROM table
WHERE column LIKE pattern ESCAPE escape_character

Here, the ESCAPE keyword indicates there is a character the server should exclude. The escape character is the actual character that you want to exclude. To demonstrate the same, we use the table data created from the code below.

CREATE TABLE NewProducts (
    ID INT IDENTITY(1,1),
    ProductName VARCHAR(50)
);

INSERT INTO NewProducts VALUES
('100% Fresh Juice'),
('70% Organic Product'),
('2L_Fresh Milk'),
('Fresh [Organic] Apples'),
('70% Banana & 30% Chocolate Milk Shake');

The following query finds all products that contain the % symbol in the product name. Here, “\” is just any other character to escape the following percentage symbol. Because it is the standard escape character in other programming languages, we used “\”. However, you can use any character, including !, ?, etc. Let me break down the %\%%’ ESCAPE ‘\ pattern.

  • % at the beginning and the end represents zero or more characters.
  • \% means escape % symbol.
SELECT ID, ProductName
FROM NewProducts
WHERE ProductName LIKE '%\%%' ESCAPE '\';
ID	ProductName
1	100% Fresh Juice
2	70% Organic Product
5	70% Banana & 30% Chocolate Milk Shake

The query below returns products whose name contains the _ symbol.  

SELECT ID, ProductName
FROM NewProducts
WHERE ProductName LIKE '%?_%' ESCAPE '?';
ID	ProductName
3	2L_Fresh Milk

Similarly, the query below returns products whose name contains [.

SELECT ID, ProductName
FROM NewProducts
WHERE ProductName LIKE '%![%' ESCAPE '!';
ID	ProductName
4	Fresh [Organic] Apples

In all the above-mentioned examples, we are escaping the default wildcards such as %, [], and _ symbols and returning the query result.

SQL NOT LIKE Operator

We can combine the LIKE operator and the NOT operator to exclude the data from the result set. The NOT LIKE operator works exactly opposite to the LIKE operator. It searches and extracts the records that do not contain or match the pattern. For example, if you want all customers except those from a city whose name starts with N. In such a case, use the NOT Operator with N%.

The syntax of the SQL NOT LIKE operator is the same as LIKE; all we have to do is add the NOT keyword before the LIKE operator in the WHERE clause.

SELECT column1, column2, ….
FROM table
WHERE column NOT LIKE pattern

Here, the NOT LIKE ignores or excludes the records that match the pattern. For example, the query below returns all products except the ones manufactured in the USA.

SELECT * FROM products
WHERE country NOT LIKE 'USA'
ProductID	ProductName	Category	Price	Country	City
2	Samsung Galaxy S25	Electronics	899.00	South Korea	Suwon
5	Adidas Atmosphere Blue Air	Footwear	120.00	Germany	Herzogenaurach
7	Lenovo Yoga Slim 9i Laptop	Electronics	2000.00	China	Beijing
9	Puma T-shirt	Clothing	65.00	Germany	Herzogenaurach
10	SJYP Jeans	Clothing	399.00	South Korea	Seoul
11	Samsung Family Hub Refrigerator	Home Appliances	399.00	South Korea	Suwon
12	Xiaomi Air Purifier H13 HEPA Filter	Home Appliances	100.00	China	Beijing

This section uses all wildcards in combination with the SQL NOT LIKE operator to show the real differences.

Using % wildcard

The following query returns all products that are not manufactured in the country whose name ends with “a”. Basically, it returns country names that do not end with “a” that include China, the USA, and South Korea. So, it displays only products from Germany.

SELECT * FROM Products
WHERE Country NOT LIKE '%a';
ProductID	ProductName	Category	Price	Country	City
5	Adidas Atmosphere Blue Air	Footwear	120.00	Germany	Herzogenaurach
9	Puma T-shirt	Clothing	65.00	Germany	Herzogenaurach

Similarly, the SQL NOT LIKE operator query below returns all products except those categories that start with E (Electronics). It displays products without Electronics.

SELECT * FROM Products
WHERE Category NOT LIKE 'E%';
ProductID	ProductName	Category	Price	Country	City
5	Adidas Atmosphere Blue Air	Footwear	120.00	Germany	Herzogenaurach
8	Air Jordan 1 High	Footwear	250.00	USA	Beaverton
9	Puma T-shirt	Clothing	65.00	Germany	Herzogenaurach
10	SJYP Jeans	Clothing	399.00	South Korea	Seoul
11	Samsung Family Hub Refrigerator	Home Appliances	399.00	South Korea	Suwon
12	Xiaomi Air Purifier H13 HEPA Filter	Home Appliances	100.00	China	Beijing

Using _ wildcard

The SQL NOT LIKE operator query below uses three _ symbols, which means three missing characters or numbers. It returns all products whose prices are not three-digit numbers. So, it excludes prices like 899, 499, 399, etc, and allows 65, 1199, 2000, and 1854.

SELECT * FROM Products
WHERE Price NOT LIKE '___.00';
ProductID	ProductName	Category	Price	Country	City
3	Apple MacBook Air Laptop	Electronics	1199.00	USA	Cupertino
4	Dell Inspiron 14 Laptop	Electronics	1854.00	USA	Round Rock
7	Lenovo Yoga Slim 9i Laptop	Electronics	2000.00	China	Beijing
9	Puma T-shirt	Clothing	65.00	Germany	Herzogenaurach

The query below combines the _ and % wildcards to return all products whose category does not contain ‘l’ as the second letter.

SELECT * FROM Products
WHERE Category NOT LIKE '_l%'
ProductID	ProductName	Category	Price	Country	City
5	Adidas Atmosphere Blue Air	Footwear	120.00	Germany	Herzogenaurach
8	Air Jordan 1 High	Footwear	250.00	USA	Beaverton
11	Samsung Family Hub Refrigerator	Home Appliances	399.00	South Korea	Suwon
12	Xiaomi Air Purifier H13 HEPA Filter	Home Appliances	100.00	China	Beijing

Using the [] and [^] wildcards

The following SQL NOT LIKE operator query returns all products whose manufacturing countries do not start with C, G, or U.

SELECT * FROM Products
WHERE Country NOT LIKE '[CGU]%'
ProductID	ProductName	Category	Price	Country	City
2	Samsung Galaxy S25	Electronics	899.00	South Korea	Suwon
10	SJYP Jeans	Clothing	399.00	South Korea	Seoul
11	Samsung Family Hub Refrigerator	Home Appliances	399.00	South Korea	Suwon

Here, we use the NOT LIKE with [^], which will act as the LIKE with []. The query below returns products manufactured in cities whose first letter is H or R.

SELECT * FROM Products
WHERE City NOT LIKE '[^HR]%'
ProductID	ProductName	Category	Price	Country	City
4	Dell Inspiron 14 Laptop	Electronics	1854.00	USA	Round Rock
5	Adidas Atmosphere Blue Air	Footwear	120.00	Germany	Herzogenaurach
9	Puma T-shirt	Clothing	65.00	Germany	Herzogenaurach

We can write the same query using the SQL LIKE operator and the [] wildcard. The following query returns the same result as the above example.

SELECT * FROM Products
WHERE City LIKE '[HR]%'
ProductID	ProductName	Category	Price	Country	City
4	Dell Inspiron 14 Laptop	Electronics	1854.00	USA	Round Rock
5	Adidas Atmosphere Blue Air	Footwear	120.00	Germany	Herzogenaurach
9	Puma T-shirt	Clothing	65.00	Germany	Herzogenaurach

Using the [-] and [^-] range wildcard

This SQL NOT LIKE operator example uses the [] and [^ ]with a range. The query below uses [G-U]%, any country whose name starts with G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, followed by zero or more characters. So, the query below returns products manufactured in the countries whose names do not start with the list mentioned above.

SELECT * FROM Products
WHERE Country NOT LIKE '[G-U]%'
ProductID	ProductName	Category	Price	Country	City
7	Lenovo Yoga Slim 9i Laptop	Electronics	2000.00	China	Beijing
12	Xiaomi Air Purifier H13 HEPA Filter	Home Appliances	100.00	China	Beijing

Here, we used [^] with a range. As we all know, two negatives make a positive. The query below returns products manufactured in countries that start with either A, B, C, D, E, F, G, and are followed by single or more characters. They are China and Germany.

SELECT * FROM Products
WHERE Country NOT LIKE '[^A-G]%'
ProductID	ProductName	Category	Price	Country	City
5	Adidas Atmosphere Blue Air	Footwear	120.00	Germany	Herzogenaurach
7	Lenovo Yoga Slim 9i Laptop	Electronics	2000.00	China	Beijing
9	Puma T-shirt	Clothing	65.00	Germany	Herzogenaurach
12	Xiaomi Air Purifier H13 HEPA Filter	Home Appliances	100.00	China	Beijing

SQL LIKE vs Equal to (=) Operator

If we use the SQL LIKE operator without any wildcard, it acts as an equal to (=) comparison operator. For example, the two queries mentioned below (without wildcard and = (equal to)) will return the same result.

SELECT * FROM Products
WHERE Country LIKE 'Germany';
ProductID	ProductName	Category	Price	Country	City
5	Adidas Atmosphere Blue Air	Footwear	120.00	Germany	Herzogenaurach
9	Puma T-shirt	Clothing	65.00	Germany	Herzogenaurach

Using the = comparison operator.

SELECT * FROM Products
WHERE Country = 'Germany';

Although both of them return the same result, they serve different purposes. The = operator to perform exact math on strings, numbers, and date fields. On the other hand, the LIKE operator is used to perform fuzzy (pattern) matching.

Use SQL LIKE Operator in CASE statement

As we mentioned earlier, apart from the WHERE clause, we can use the LIKE operator in the SELECT statement. The following query uses the LIKE operator in the CASE statement to find the email providers. Here

  • %@gmail% checks any part of the EmailAddress column contains @gmail. If so, it is a Gmail Account.
  • The same approach applies to Hotmail, Yahoo, and Adventure Works.
  • If all of them fail, mention them as Other provider.
SELECT [SNo],[Name],[EmailAddress]
    ,CASE
        WHEN EmailAddress LIKE '%@gmail%' THEN 'Gmail Account'
        WHEN EmailAddress LIKE '%@hotmail%' THEN 'Hotmail Account'
        WHEN EmailAddress LIKE '%@yahoo%' THEN 'Yahoo Account'
        WHEN EmailAddress LIKE '%@adventure-works%' THEN 'Internal'
        ELSE 'Other Provider'
    END AS [Email Providers]
FROM [EmpDetails]
SNo	Name	EmailAddress	Email Providers
1	Guy Gilbert	guy1@adventure-works.org	Internal
2	Kevin Brown	kevin0@adventure-works.org	Internal
3	Roberto Tamburello	roberto0@tutorialgateway.org	Other Provider
4	Rob Walters	rob0@tutorialgateway.org	Other Provider
5	Willis Johnson	willis0@adventure-works.org	Internal
6	Thierry D'Hers	thierry0@adventure-works.org	Internal
7	David Bradley	david0@gmail.com	Gmail Account
8	Linda Moschell	linda1@adventure-works.org	Internal
9	JoLynn Dobney	jolynn0@tutorialgateway.org	Other Provider
10	Ruth Ellerbrock	ruth0@hotmail.com	Hotmail Account
11	Gail Erickson	gail0@hotmail.com	Hotmail Account
12	Barry Johnson	barry0@adventure-works.org	Internal

Using SQL LIKE Operator in subqueries

We can also use the LIKE operator in subqueries or nested queries to perform a wildcard search on an internal query. Here, the subquery selects the country id whose name starts with U (USA and UK). The main query selects the customer ID and name from the USA and the UK.

SELECT [CustomerID],[Name]
FROM [customers]
WHERE [CountryID] IN 
(
    SELECT [CountryID] FROM Countries
    WHERE Country LIKE 'U%'
);
CustomerID	Name
1	John Smith
4	David Williams

SQL LIKE Operator vs IN Operator

Some people may confuse the LIKE operator and the IN operator, thinking they are both the same. The IN operator allows you to search for multiple values placed within the brackets (). On the other hand, the LIKE operator is used to perform a wildcard search using partial text or a substring.

The query below returns products manufactured in the city whose name starts with B. They are Beijing and Beaverton.

SELECT * FROM Products
WHERE City LIKE 'B%'

To get the same result in the IN operator, we must specify the names explicitly.

SELECT * FROM Products
WHERE City IN('Beijing', 'Beaverton')
ProductID	ProductName	Category	Price	Country	City
7	Lenovo Yoga Slim 9i Laptop	Electronics	2000.00	China	Beijing
8	Air Jordan 1 High	Footwear	250.00	USA	Beaverton
12	Xiaomi Air Purifier H13 HEPA Filter	Home Appliances	100.00	China	Beijing
Categories SQL