MySQL: LIKE Operator

Profile picture for user arilio666

To identify and specify certain patterns among the table the LIKE operator is used in MySQL. LIKE operator in MySQL works based on the operation of certain wildcards such as % and _ each has its unique operations and coupled with the WHERE clause.

% sign signifies multiple characters of any kind whereas _ sign signifies a single character.

Syntax

SELECT * from TableName WHERE ColumnName LIKE value;

Let us have a look at how it is conveyed using the wildcards:

WHERE ColumnName LIKE 'a%'; -- Finds anything that starts with the letter 'a'
WHERE ColumnName LIKE '%a'; -- Finds anything that ends with the letter 'a'
WHERE ColumnName LIKE '%a%'; -- Finds anything that has 'a' in any sort of order
WHERE ColumnName LIKE '_a'; -- Finds the character which has two characters long pertaining with the number of underscores provided.
WHERE ColumnName LIKE 'a____b'; -- Finds word starting with 'a' and followed with 4 underscores of characters and ending with 'b'

So for demo purposes, we will be using the customers table as usual and we will be doing some operations based on LIKE.

Here is what we will do:

  1. Select the table
  2. Fetch address containing trail or avenue.
  3. Fetch phone numbers that end with 9.  

Example

select * from customers where address like '%trail%' or address like '%avenue%' 

So as we can see clearly I have used the wildcards % to find anything that contains trail and avenue in the address column and it has fetched according to our query too.

select * from customers where phone like '%9'

Same we wanted numbers ending with 9 and so it has fetched with the wildcard in the beginning to get any number ending with 9.

Let us see some examples on _ wildcard.

select * from customers where last_name like '_____y'

So we have queried with 5 _'s to fetch the last name column with 6 character names ending with 'y' and so on it has done the job of fetching words ending with 'y' with 5 character origin.

Tags