Blog Bustle 14/03/2020
Like operator in sql is used with where clause to filter results where column, which is given in condition, matches with like pattern.
In MySQL, with like operator, we use two wildcards in conjunction.
1. % [percent] : It represents zero, one or multiple numbers/characters.
2. _ [underscore]: It represents single number/character.
The basic syntax of like operator in sql
select * from table_name
where column_x like pattern;
select column_1, column_2, column_3, ....
from table_name
where column_x like pattern;
Here are some examples of pattern which will help you to understand use of like operator in sql.
select * from table_name where column_x like 'a%';
select * from table_name where column_x like '%a';
select * from table_name where column_x like '%a%';
select * from table_name where column_x like 'a%b';
select * from table_name where column_x like '_m%';
select * from table_name where column_x like 'm__%';
For example, I have student table.
id | name | city | state |
---|---|---|---|
1 | gaurav | agra | uttar pradesh |
2 | robin | agra | uttar pradesh |
3 | neha | amritsar | punjab |
4 | gurmeet | patiala | punjab |
I want to find all records where state starts with 'p' then sql query will be
select * from student where state like 'p%';
id | name | city | state |
---|---|---|---|
3 | neha | amritsar | punjab |
4 | gurmeet | patiala | punjab |
I want to find all records where city ends with 'r' then sql query will be
select * from student where city like '%r';
id | name | city | state |
---|---|---|---|
3 | neha | amritsar | punjab |
I want to find all records where name have 'ur' character in any position then sql query will be
select * from student where name like '%ur%';
id | name | city | state |
---|---|---|---|
1 | gaurav | agra | uttar pradesh |
4 | gurmeet | patiala | punjab |
I want to find all records where name starts with 'g' and have 'm' character in 4th position then sql query will be
select * from student where name like 'g__m%';
id | name | city | state |
---|---|---|---|
4 | gurmeet | patiala | punjab |
Remember that first select database then run any sql query related to that database otherwise you will get 'no database selected' error.
So we can easily understand use of like operator in sql by above examples. Hope you like this tutorial.
Sql Insert Query
Sql Update Query
Sql & Mysql difference
Sql Delete Query
Sql Select Query
Mysql no database selected error 1046
Sql Create Table
Sql Drop Table
Sql Alter Query(add, drop, modify & change column)
Sql Truncate Table
Sql Insert Into Query
Sql Rename Table
Sql DISTINCT Keyword
Sql WHERE Clause
Sql Like operator
Sql AND operator
Sql OR operator
Sql NOT operator
Combining Sql AND, OR & NOT operator
Sql IN operator
Sql NOT IN operator
Sql Count, Sum, Avg, Min, Max
Sql Order By clause
Sql Limit clause
Sql BETWEEN operator
Sql Group By clause
Sql Aliases
Sql Inner Join
Sql Left Join
Sql Right Join
Mysql Create Database
Mysql Create Database Command Line
Mysql Delete Database
Mysql Delete Database Command Line
Mysql Show Databases
Mysql Show Databases Command Line
Mysql Select Database
Mysql Select Database Command Line
Mysql Backup Database
Mysql Import Database
Mysql Copy Database
Mysql Rename Database
Truncate Database Mysql