Written by Sai Sameera Thota on September 29, 2013 in How to > Programming > SQL

How to use SQL Like operator

GET ALERTS:

Get our Latest updates delivered to your mailbox!

SQL LIKE operator is used to search for a string with specific pattern. It uses wildcard characters ‘%’ and ‘_’ to search for a pattern in the string. % represents a single or multiple characters whereas ‘_’ represents a single
character, let us see How to use SQL Like operator.

How to use SQL LIKE operator

Let us consider a table which contains student details.  The table contains first name, last name and the age of students.

mysql> select * from details;

+------------+-----------+------+--------

| first_name | last_name | age |

+------------+-----------+------+--------

| Ram        | Kumar     | 20 |

| Swati      | Pillai    | 20 |

| John       | Mathews   | 30 |

| Sandeep    | Thota     | 15 |

| Arthi      | Singh     | 22 |

+------------+-----------+------+----------

5 rows in set (0.07 sec)

The students whose first names begin with J are displayed using the following query.

mysql> select first_name from details where first_name LIKE 'J%';

+----------------+

| first_name |

+----------------+

| John       |

+----------------+

1 row in set (0.08 sec)

In the above query, LIKE operator is used along with the wild card character %. % signifies multiple characters. Hence the statement ‘J%’ represents that the string should begin with the letter J followed by multiple characters.

The students whose first name ends with the letter ‘i’ are displayed using the following query

mysql> select first_name from details where first_name LIKE ‘%i’;

+----------------+

| first_name |

+----------------+

| Swati      |

| Arthi      |

+----------------+

2 rows in set (0.00 sec)

In the above query the letter I is preceded by wild card character % which represents that the string should end with the letter ‘i’ which can be preceded by multiple characters.

The wildcard character % is not only used with single character but also along with strings. Let us consider the following example.

mysql> select * from details where first_name LIKE '%deep';

+----------------+---------------+------+

| first_name | last_name | age |

+----------------+---------------+------+

| Sandeep     | Thota    | 15 |

| Sandeep     | Singh    | 34 |

+----------------+---------------+------+

2 rows in set (0.11 sec)
mysql> select * from details where last_name LIKE '%in%';

+----------------+---------------+------+

| first_name | last_name | age |

+----------------+---------------+------+

| Arthi      | Singh     | 22 |

| Sandeep    | Singh     | 34 |

+---------------+----------------+------+

2 rows in set (0.00 sec)

As mentioned earlier, the wildcard operator ‘_’ signifies a single character. The query given below displays all the students’ details who has letter ‘a’ in the second position of their first names.

mysql> select * from details where first_name LIKE '_a%';

+----------------+---------------+------+

| first_name | last_name | age |

+----------------+---------------+------+

| Ram        | Kumar      | 20 |

| Sandeep    | Thota      | 15 |

| Sandeep    | Singh      | 34 |

+---------------+----------------+------+

3 rows in set (0.00 sec)

The next query is to display all the students’ details whose first name has 5 characters.

mysql> select * from details where first_name LIKE '_____';

+----------------+---------------+------+

| first_name | last_name | age |

+----------------+---------------+------+

| Swati      | Pillai    | 20 |

| Arthi      | Singh     | 22 |

+----------------+---------------+------+

2 rows in set (0.00 sec)

In the above query 5 underscore ( _ ) characters are  used to represent a 5 character string.

The NOT LIKE operator in SQL is used to display the records which doesn’t match the given pattern.

mysql> select * from details where first_name NOT LIKE 'S%';

+----------------+---------------+------+

| first_name | last_name | age |

+----------------+---------------+------+

| Ram        | Kumar     | 20 |

| John       | Mathews   | 30 |

| Arthi      | Singh     | 22 |

+---------------+-------------+------+

3 rows in set (0.07 sec)

The above query displays details of all the students except the students whose first names begin the character ‘S’.

{ 0 comments… add one now }

Leave a Comment

Previous post:

Next post: