Like operator in sql

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.

Syntax

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.

A. column_x value starts with 'a'

select * from table_name where column_x like 'a%';

B. column_x value ends with 'a'

select * from table_name where column_x like '%a';

C. column_x value can have character 'a' in any position

select * from table_name where column_x like '%a%';

D. column_x value starts with 'a' and ends with 'b'

select * from table_name where column_x like 'a%b';

E. column_x value where m is in second position

select * from table_name where column_x like '_m%';

F. column_x value starts with 'm' and have atleast 3 characters

select * from table_name where column_x like 'm__%';

For example, I have student table.

I want to find all records where state starts with 'p' then sql query will be

select * from student where state like 'p%';

I want to find all records where city ends with 'r' then sql query will be

select * from student where city like '%r';

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%';

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%';

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.

Other important links

Sql where clause

Sql distinct keyword

Sql rename table query

Insert into select query

Like operator in sql searches:

Tags: #mysql