DATA with BARAA

SQL LIKE

In this tutorial, you will learn how to retrieve the data that matches a pattern using the SQL operator LIKE within the WHERE clause.

The LIKE is one of the logical operators in SQL that returns TRUE if a value matches a pattern or FALSE  otherwise.

AND Return True if both conditions are True
OR Return True if one of conditions is True
NOT Reverse the result of any Boolean operator
IN Return True if a value is in a set of values
BETWEEN Return True if a value falls within a specific range
LIKE Return True if a value matches a pattern

The SQL provides you with two wildcard characters to build up a pattern:

  • The percent sign (%) – matches anything, zero, one or multiple characters.
  • The underscore (_) – matches exactly one character.

This is not easy to understand lets have some examples.

Syntax

The basic syntax of the WHERE clause to filter the data returned by a query can be given with:

				
					SELECT column_names
FROM table_name
WHERE column_name LIKE value
				
			
.
.

Examples

To understand the LIKE statement in a better way, let’s look at the following customers table in our tutorial database:

Customers
customer_id first_name last_name country score
1 Maria Cramer Germany 350
2 John Steel USA 900
3 Georg Pipps UK 750
4 Martin Müller Germany 500
5 Peter Franken USA NULL

Now, let’s check out some examples that demonstrate how it actually works.

We have the following task be to solve using SQL statements

The following SQL statement will returns all customers from customers table and order the result by the score column in ascending order.

You can have the same the result set by skipping the ASC, because it is the default option in ORDER BY.

				
					SELECT *
FROM customers
WHERE first name LIKE 'M%'
				
			

After executing the above query, you’ll get the result set something like this:

Find all customers whos name starts with 'M'
customer_id first_name last_name country score
1 Maria Cramer Germany 350
2 John Steel USA 900
3 Georg Pipps UK 750
4 Martin Müller Germany 500
5 Peter Franken USA NULL

As you can see the output contains everything the whole customers tables including all rows and columns.

Similarly, you can use the DESC option to perform a sorting in descending order. The following statement will orders the result set by the numeric salary column in descending order.

Find all customers whos name ends with 'n'
				
					SELECT *
FROM customers
WHERE first name LIKE '%n'
				
			
Find all customers whos name contains 'r'
				
					SELECT *
FROM customers
WHERE first name LIKE '%r%'
				
			

Once you specify multiple columns after ORDER BY, the Database will sort the result by the first column, then the new ordered list will be sorted again by next column.

Operators in WHERE Clause

Find all customers whos name contains 'r' in 3d position

You can filter your results in a number of ways using comparison and logical operators, which you’ll learn about in the next tutorials. I summarized in the following table the most important ones.

				
					SELECT *
FROM customers
WHERE first name LIKE '__r%'
				
			
.
.
Share it !