Quick Reach
The Like operator of SQL
Following are the main points about the SQL Like operator:
- The Like is an operator in SQL that is used to search columns data by the specified criteria.
- The like operator uses the wildcard character(s) to search in a column.
- The SQL wildcards are % (percentage) and _ (underscore).
- The % is a wildcard that substitutes for zero or more characters whereas ‘_’ substitutes single character.
- You can search text as well as numbers by using the Like SQL operator.
See Like with % example
Like with _ operator example
A Like Operator Short video
Syntax of the like operator
The general syntax of using the SQL like operator is as follows:
Like With %
Select * from table_name
Where col_name like ‘abc%’
Like With _
Select * from table_name
Where col_name like ‘abc_’
Where you can place ‘%’ or ‘_’ before or after the search character or string.
Placing % before the given search term in like operator means any data found in the given column ending with ‘abc’ will be returned.
e.g. xyzabc, 123abc
Whereas placing the % after the given search term in the like operator means any data found in the given column starting with ‘abc’ will be fetched.
Example of using the Like operator
Let us show you examples of the Like operator with our created database and table. We will use the same database, test_db, and tbl_employee table to show a few examples of the like with wildcard.
Just to remind this is how our example table is created in test_db:
|
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)
);
|
Example of Like with % wildcard at the starting letter
The following example shows how to use the % (a like wildcard) with the like operator to find the employees whom names are starting with the letter ‘M’.
The SQL query:
|
1
2
3
|
select * from tbl_employee
Where emp_name like ‘M%’
|
Demo table with Select Like
Example of Like with % wildcard at the ending letter
The example below shows using the % wildcard with the like operator to find the employees whom names are ending with the letter ‘n’.
The query with like operator:
|
1
2
3
|
select * from tbl_employee
Where emp_name like ‘%n’
|
Demo table with Like wildcard
Example of Like with % wildcard anywhere in a search term
Placing the % (wildcard) operator on the left and right of the given search term will search from the start and anywhere within the words to return the resultset.
The SQL query with like:
|
1
2
3
|
select * from tbl_employee
Where emp_name like ‘%m%’
|
See this example online
Example of Like with % wildcard with number
This example returns all employees data whom salary is starting with digits 45 by using the SQL wildcard (%).
The query:
|
1
2
3
|
select * from tbl_employee
Where emp_salary like ‘45%’
|
See this example online
Select – Like example with _ wildcard at starting letter
The example below shows using the _ (like wildcard) with the like operator to find the employees from tbl_employee table whom names are starting with letters ‘Mik’. It will only substitute one character.
The query with like is:
|
1
2
3
|
select * from tbl_employee
Where emp_name like ‘Mik_’
|
See this example online
As you can see, it only fetched one row.
Leave A Comment?