The Like operator in MySQL
MySQL Like operator is used to search table columns data. It uses ‘_’ and ‘%’ wildcards to search in tables. The % (modulus) wildcard substitutes zero or more characters at any side of the search term. While _ (underscore) wildcard substitutes one character.
You can see examples of using these wildcards with Like in MySQL at the later part of this tutorial.
Like example with’_’
Also, you can use the Like operator to search not only text based columns data but also numeric columns as well.
Like operator Syntax
Following is the syntax to use MySQL Like with both wildcards.
Using Like with % (modulus) wildcard
Select * from table_name
Where col_name like ‘abc%’
Using Like with _ (underscore) wildcard
Select * from table_name
Where col_name like ‘_abc_’
You can see in the above syntax that wildcards % and _ can be used at either side of search character. To make it clearer look at the following examples of using Like with wildcards.
Like operator examples
For our examples to explain the like operator, we have a table, tbl_employee where we also entered some test data. This is how our test table is created.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
Create table tbl_employee
(emp_id int,
emp_name varchar(100),
emp_age int,
emp_salary decimal(8,2),
joining_date datetime,
PRIMARY KEY (emp_id)
);
|
You can see the table is created with five columns.
Using Like and % example
Following example uses % operator with MySQL Like operator. In that example, we will place % wildcard after the search character. That means the Like query will return the rows for given column that starts with letter ‘M’. Click on the link to see the output of the query as well.
The Like query of MySQL:
|
1
2
3
|
select * from tbl_employee
Where emp_name like ‘M%’
|
Like and % wildcard at ending letter
Placing % wildcard before search string or character will return records that end with the given character, found in the specified column. The query and output are shown below:
|
1
2
3
|
select * from tbl_employee
Where emp_name like ‘%n’
|
Using % at both sides example
In this example. we will use % wildcard at both sides of the search term. That means the Like query will search the specified column data and return all rows where search term found in the text. To make it clearer see the following query and click on the link to see the output of the query.
The MySQL Like query with % at both sides:
|
1
2
3
|
select * from tbl_employee
Where emp_name like ‘%m%’
|
Like and % with numeric search example
As mentioned earlier, you can also search numeric columns by using the Like operator with wildcards. The following example will return employees records whom salary is starting with 45.
The Like query for numeric column:
|
1
2
3
|
select * from tbl_employee
Where emp_salary like ‘45%’
|
An example of Like with _ (underscore)
As explained in the earlier part of this tutorial, the underscore (_) wildcard substitutes single character in Like query. Following example shows how to use _.
The Like query with _:
|
1
2
3
|
select * from tbl_employee
Where emp_name like ‘Mik_’
|
As you can see it only fetched a row after substituting single character.
Leave A Comment?