Understanding SQL Server NOT LIKE: A guide for Dev

Hello Dev! Are you familiar with SQL Server NOT LIKE? If not, then this article is for you. In this guide, we’ll cover everything you need to know about SQL Server NOT LIKE, including its syntax, usage, and common examples. So, let’s get started!

What is SQL Server NOT LIKE?

SQL Server NOT LIKE is a logical operator that is used to filter data based on a pattern that does not match the specified string. This operator is commonly used in conjunction with the WHERE clause to filter out unwanted data from a database table.

The syntax for the SQL Server NOT LIKE operator is as follows:

Using SQL Server NOT LIKE

To use the SQL Server NOT LIKE operator, you must specify the column name and the pattern to be filtered. The pattern can contain special wildcard characters that represent any character or a specific set of characters.

The following table shows the wildcard characters that can be used with SQL Server NOT LIKE:

Example:

Let’s consider the following example:

SELECT * FROM EmployeesWHERE FirstName NOT LIKE 'J%'

This SQL statement returns all employees whose first name does not start with the letter ‘J’.

Common Examples of SQL Server NOT LIKE

Here are some common examples of SQL Server NOT LIKE:

Example 1:

SELECT * FROM ProductsWHERE ProductName NOT LIKE '%apple%'

This SQL statement returns all products whose name does not contain the word ‘apple’.

Example 2:

SELECT * FROM CustomersWHERE City NOT LIKE 'New%'

This SQL statement returns all customers whose city name does not start with the word ‘New’.

Example 3:

SELECT * FROM OrdersWHERE ShipCountry NOT LIKE '[USA, Canada, Mexico]'

This SQL statement returns all orders whose shipping country is not in the specified set of countries.

FAQ About SQL Server NOT LIKE

Q: Can I use SQL Server NOT LIKE with the IN operator?

A: Yes, you can use SQL Server NOT LIKE with the IN operator. For example:

SELECT * FROM CustomersWHERE City NOT IN ('New York', 'Los Angeles', 'Chicago')

This SQL statement returns all customers whose city is not ‘New York’, ‘Los Angeles’, or ‘Chicago’.

Q: Can I use SQL Server NOT LIKE with the EXISTS operator?

A: Yes, you can use SQL Server NOT LIKE with the EXISTS operator. For example:

SELECT * FROM ProductsWHERE NOT EXISTS (SELECT * FROM Categories WHERE Products.CategoryID = Categories.CategoryID)

This SQL statement returns all products that do not have a corresponding category in the Categories table.

Q: Can I use SQL Server NOT LIKE with the BETWEEN operator?

A: No, you cannot use SQL Server NOT LIKE with the BETWEEN operator. The BETWEEN operator is used to filter data within a range of values, while the NOT LIKE operator is used to filter data based on a pattern that does not match the specified string.

Q: Can I use SQL Server NOT LIKE with the ORDER BY clause?

A: Yes, you can use SQL Server NOT LIKE with the ORDER BY clause. For example:

SELECT * FROM CustomersORDER BY City NOT LIKE 'New%', City

This SQL statement sorts the customers by city name. If the city name starts with the word ‘New’, it is placed at the end of the list.

Conclusion

SQL Server NOT LIKE is a powerful operator that allows you to filter data based on a pattern that does not match the specified string. It can be used in various scenarios, such as filtering product names, customer cities, and shipping countries. We hope this guide has been helpful to you. Happy coding!