PHPGurukul

MySQL LIKE

The comparison operators discussed so far have compared one value, exactly to one other value. Such precision may not always be desired or necessary. For this purpose MySQL provides the LIKE.

The LIKE allows comparison of one string value with another string value, which is not identical. This is achieved by using wildcard characters. Two wildcard characters that are available are :

For character data type:

  • % allows to match any string of any length (including zero length).
  • _ allows to match on a single character.

tblemployee table

idFnameLnameDepartment
1AnujKumarInformation Technology
2AnkitSinghFinance
3JhonDoeHR

Example 1 :

SELECT id,Fname,Lname,Department FROM tblemployee WHERE Fname LIKE 'An%';

Output

idFnameLnameDepartment
1AnujKumarInformation Technology
2AnkitSinghFinance

In the above example all those records where the value held in the field Fname begins with An are displayed. The % indicates that any number of characters can follow the letters An.

Example 2:

List the employees whose names have the second character as n and h.

SELECT id,Fname,Lname,Department FROM tblemployee WHERE Fname LIKE '_n%' OR Fname like '_h%';

Output

idFnameLnameDepartment
1AnujKumarInformation Technology
2AnkitSinghFinance
3JhonDoeHR

In the above example, all those records where the value held in the Fname contains the second character as n or h are displayed. The _n and _h indicate that only one character can precede the character n or h. The % indicates that any number of characters can follow the letter An.

Example 3:

List the employees whose name start with the letters Jh and it is a four letter word.

SELECT id,Fname,Lname,Department FROM tblemployee WHERE Fname LIKE '_n%' OR Fname like 'Jh__';(i.e. two underscore characters)

Output

idFnameLnameDepartment
3JhonDoeHR

In the above example, all those records where the value held in the field Fname begins with Jh are displayed. The __(i.e. two underscore characters) indicates that only two characters can follow the letters Jh. This means the whole word will only be four characters.

Anuj Kumar

This is Anuj Kumar. I’m a professional web developer with 4+ year experience. I write blogs in my free time. I love to learn new technologies and share with others.
I founded PHPGurukul in September 2015. The main aim of this website to provide PHP, Jquery, MySQL, PHP Oops and other web development tutorials.
.

Recommended Tutorials for you


Add comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.