Mysql Find Rows Where Any Column Matches a Search Pattern

When developers think of searching records in a database, they often think of finding matching values whether they are numerical, calculated values or a matching string of characters. However, the MySQL programming language also has a way to find records that do not match a specific value. The feature is essentially the opposite of find a list of matching values, which means you can get two recordsets: a recordset with matching values and ones that do not have the values. The advantage is that you can find records that you need to edit or report on a spreadsheet. To find out what else this language can do, you might want some MySQL Training for Beginners.

An Overview of the Not Like Statement

The not like statement does exactly what it sounds like it does. It searches records that do not match the value in the not like clause. The advantage of using the like statement is that you can use wildcards, so you can search for a range of values or values that match a pattern. The not like statement is different from the equals statement, because the equals statement requires that you enter an exact value found in the MySQL table field.

When you're learning the MySQL language, there are certain where clauses you need to know, and the "not like" statement is used enough where it should be a part of your common MySQL query statement knowledge. The not like statement is not used as much as other statements, but it's used to filter out records after they have been retrieved from the database tables.

Creating a Simple MySQL Statement

The first step in creating a MySQL not like statement is first determining what tables you want to search. You can search only one table, or you can use a MySQL join statement to search multiple records that "connect" to each other between each table. Regardless of how many tables you want to search, the not like statement is in the search phrase's where clause, which you can set up after you create a basic MySQL statement.

After you figure out the tables you want to search, set up a basic MySQL search query. The following code is a simple MySQL statement that retrieves all records from the MySQL table named customers:

select * from customers

You can also specify specific fields in the table. For instance, if you want to query only the first and last name of customers in the customers table, you use the following MySQL syntax:

select first_name, last_name from customers

It's generally frowned upon in the programming world to use the asterisk to return all rows. You can generally get away with it when you do one-off queries to quickly look at records, but most database admins will request that you always specify the fields you want to return. This includes if you want to return multiple fields from different tables joined together in the MySQL statement.

Need to integrate MySQL into a PHP app? Here's a good place to start.

Adding the Not Like Clause to the MySQL Statement

After you set up the basic statement, you can add the where clause statement. The where clause is where you filter records. You can one of multiple where clause statements. You can also mix and match logic conditions such as using the "or" or "and" keywords. Where clauses can get very complicated if you add several logical conditions. You can also use parentheses to group clauses to make them easier to read.

The following code adds a basic not like where clause to the main MySQL statement:

select first_name, last_name from customers where first_name not like 'mike'

In the above statement, MySQL returns all records where the first_name column does not equal "mike." Notice there are no wildcard characters in the not like statement. In this instance, MySQL will return any column where the first_name is not exactly "mike." This means that any column that equals "mikee," "mikke," or "mike " will also return. Even the last one in the example will return, because of the appended space added to the "mike" string.

To fix this issue and remove all records that match the phrase "mike" you add the wildcard character. The wildcard character is the percent sign ( % ). You can use the wildcard in the beginning, end or even between characters in the string. For instance, the following code returns all records that have "mike" appended to the end of any string:

select first_name, last_name from customers where first_name not like '%mike'

Because the wildcard is in the front of the string and not the back, MySQL will only return records that have "mike" at the end with no additional characters appended. You can use this the opposite way and only return records that have "mike" at the front of the string. To do this query, use the following syntax:

select first_name, last_name from customers where first_name not like 'mike%'

Just like the first example, this not like statement only returns records that do not contain "mike" as the first four characters in the first_name column.

You can also use the wildcard characters to find all records that do not contain "mike" anywhere in the character string. The following MySQL syntax looks for any customer in the first_name column that contains "mike" anywhere in the string. Because you are using the not like statement, it filters these records out of the record set. The following is the syntax to execute the MySQL logic statement:

select first_name, last_name from customers where first_name not like '%mike%'

Joining Tables

You sometimes need to join tables to perform a query, even a not like query. You can filter records and link tables using the join statement. You can join one or several tables, although too many joins without proper table indexing can cause performance issues. Your database admin usually creates the tables' indexes. Indexes should be created on columns that you want to use to join. For instance, if you want to join the customers table to the orders table, your MySQL database admin will have an index on the customer id field in the customer table, and the foreign key customer id field in the orders table. Indexes on joined columns are imperative for your applications' performance.

To set up a query with a joined table and the not like statement, you first need to create the basic query in the same way you set up the query with no joined tables. The following MySQL statement joins the customers and the orders table together using the customer id primary and foreign keys:

select first_name, last_name from customers left join orders on (customer_id = customer_id)

In this statement, notice the "left join" syntax. This means take all of the customers on the left side of the statement and join with the orders table. Because you specify to return all records from the customer table, even customers with no orders are also return, except the data for those orders are set to null. If you want to only return customers with orders, then you replace "left join" with "inner join." With an inner join, customers with no order matches are filtered out.

After you create the main statement with the joined tables, you add the not like clause. With joined tables, you can use columns in any of the tables in the not like clause. The following code uses the orders table to filter out any records where the product does not contain the string "red" in the column:

select first_name, last_name from customers left join orders on (customer_id = customer_id) where product not like '%red%'

Learn some more advanced MySQL topics such as joining tables for better database programming skills.

Combining Multiple Not Like Clauses

You can also combine multiple not like clauses using the MySQL and and or statements. You can use both of these logic operators within one statement. For instance, if you want to exclude all products that contain the "red" string and any records where the customer's name contains the "mike" string, you can use the following query:

select first_name, last_name from customers left join orders on (customer_id = customer_id) where product not like '%red%' and first_name not like '%mike%'

Combining the statements with the and statement, the above MySQL statement filters out all records where the user has a first name with "mike" and purchased a product with the "red" phrase.

You can also use the "or" statement. The or statement is tricky for some developers, because it will return all records that match either of the statements in the where clause. For instance, take the following MySQL statement using the or clause:

select first_name, last_name from customers left join orders on (customer_id = customer_id) where product not like '%red%' or first_name not like '%mike%'

In the above statement, MySQL will return any records that don't contain "red" or "mike," which is essentially all of the records. First, MySQL looks at the first statement and returns records that don't have "red" in them. Then, it gets the records that don't have "mike" in them. Since the records that contain red don't contain mike, then all records are essentially returned.

You can also combine multiple statements in the same MySQL query. You can then control the way the logic is read using parenthesis. For instance, the following code combines two or statements and one and statement:

select first_name, last_name from customers left join orders on (customer_id = customer_id) where (product not like '%red%' or first_name not like '%mike%') and product not like '%blue%'

In the above statement, the first set of or statements are evaluated. In this example, all records are returned. After the or statement is evaluated, the and statement is executed. The and statement combines with the or statement and filters out all records that contain the string "blue" in the product name.

Notice the parenthesis. The parenthesis controls the query flow of execution. If you removed the parenthesis, the order would be the same order in which the statements are set out in the MySQL query.

The not like MySQL statement is a useful way to filter out records you don't want in your record sets. You can also match the not like statement with other where clause statements such as the equals, like, where, in, and not in statements.

When you combine this statement with any join statement on your tables, you can further add or remove records, depending on the type of logic and record sets you need returned from the query. For new developers, you might need to adjust and test the query string before you return the right record sets. This is especially true when you need to combine multiple logic operators together in one MySQL query.

If you want to understand MySQL, the not like clause is an imperative part of your understanding of the MySQL language. It isn't used as much as other clause statements, but it can be a powerful way to filter out record sets when you need to return a large collection and then filter out known values.

If you're enjoying learning more about MySql, it might be time to try an introduction to database design.

Mysql Find Rows Where Any Column Matches a Search Pattern

Source: https://blog.udemy.com/mysql-not-like/

Share this post