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

id	Fname	Lname	Department
1	Anuj	Kumar	Information Technology
2	Ankit	Singh	Finance
3	Jhon	Doe	HR

Example 1 :

Output

id	Fname	Lname	Department
1	Anuj	Kumar	Information Technology
2	Ankit	Singh	Finance

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.

Output

id	Fname	Lname	Department
1	Anuj	Kumar	Information Technology
2	Ankit	Singh	Finance
3	Jhon	Doe	HR

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.

Output

id	Fname	Lname	Department
3	Jhon	Doe	HR

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.