15 - MySQL Like Clause

LIKE is a very useful operator in MySQL to fetch the data with conditions from the table. LIKE operator is often used with WHERE clause in the select statement to fetch the data from the MySQL table. Basic Syntax of like operator is shown as:

SELECT Firstname, LastName, Address
FROM Student
WHERE LastName LIKE 'ka%';

The above command fetches the First name, Last name and Address of all the students whose name starts with letter “ka”.

MySQL provides two wildcard characters used with LIKE operators, these are percentage ( % ) and Underscore ( _ ).

  • Percentage ( % ) allows you to compare any string of zero or more characters .
  • Underscore ( _ ) allows you to compareany single characters.

We can use these wildcards in the beginning, in the end or in the middle of the value to compare the string.

Use of percentage as wildcard

Percentage is used for matching any number of characters in the beginning or in the end of given string. For example we have a data of the company names along with their address and price of some server.

ID

Company

Address

Amount

1

HCL

NY

$1000

2

Apple

Chicago

$2000

3

IBM

INDIA

$3000

4

Asus

Singapore

$15000

Now, we want to fetch the Company, Address and Amount from the company table where company name starts with letter “A”.
   SELECT ID, Company, Address, Amount from company where company like ‘A%’;

The result of the above like query is:

ID

Company

Address

Amount

2

Apple

Chicago

$2000

4

Asus

Singapore

$15000

Another example, If we need all rows having Address ends with letter “go”
    SELECT ID, Company, Address, Amount from company where company like ‘%go’;

Then the result is:

ID

Company

Address

Amount

2

Apple

Chicago

$2000

Use of Underscore as wildcard

Underscore is used to match the specified number of characters in the matching criteria. For example we have tablestudents with the information of their Name, Address and class

.

ID

Name

Address

Class

1

Aman

Faridabad

Computers

2

John

Delhi

Computers

3

Salman

Jaipur

Computers

4

Manish

Kerala

Computers

Now we need to find students whose names are four characters long, then the like operator is used as:
    SELECT ID, Name, Address, class from students where name like ‘____’;

Here we use four underscores to denote four characters in the name. The result of above select query is:

ID

Name

Address

Class

1

Aman

Faridabad

Computers

2

John

Delhi

Computers

Another example, we want to fetch students whose Address starts with D and is of 5 letter word. The like operator is used as:
      SELECT ID, Name, Address, class from students where Address like ‘D___’;

Here we use first character as D and then four consecutive underscores, so total characters is 5 and the address starts with the letter D. The result of above select query is:

ID

Name

Address

Class

2

John

Delhi

Computers

Note: Like operator slows down the query because like operator not using the complete index in MySQL select query.

Like us on Facebook

Wideskills