SQL LIKE Clause

    With SQL LIKE clause, we can match a specific pattern from a data value, we mostly use LIKE clause for search purpose. With LIKE operator we use two wildcards, and these two wildcards are used to create search patterns:

    1. % (Percentage)- This symbol represents zero or more than zero characters.
    2. _ (Underscore) - This symbol represents a single character.

    LIKE operator Syntax

    To use LIKE operator, follow this syntax:

    SELECT column_name_1, column_name_2, ...
    FROM table_name
    WHERE column_name LIKE search_pattern;

    Syntax Example With the help of two wildcards of LIKE operators, we can generate different search patterns.

    Wildcard Pattern Description
    %
    'ai%'
    Look for that data which value starts with “ ai
    %
    '%ai%'
    Look for that data which have ai in it
    _ %
    '_ai%'
    Look for that data which second value is a and third value is i.
    _ % _ %
    'a_%_%'
    Look for that data which value starts with a and have at least 3 characters.
    %
    '%a'
    Look for that data which value ends with a
    _ %
    '_a%i'
    Look for that data which 2 nd value is a and ends with i
    _
    'a___i'
    Look for a value which total length is 5 and its stating value is a and ending value is i

    SQL Like Example

    For the example considered this Students table:

    +------+--------+------+--------+-------+
    | id   | name   | age  | grades | marks |
    +------+--------+------+--------+-------+
    |    1 | Luffy  |   16 | A      |   970 |
    |    2 | Naruto |   18 | A      |   960 |
    |    3 | Zoro   |   20 | A      |   940 |
    |    4 | Sanji  |   21 | B      |   899 |
    |    5 | Nami   |   17 | B      |   896 |
    |    6 | Robin  | NULL | B      |   860 |
    +------+--------+------+--------+-------+

    Query: Display those students records whose name starts with N

    SELECT *
    FROM students
    WHERE name LIKE 'N%';

    Output

    +------+--------+------+--------+-------+
    | id   | name   | age  | grades | marks |
    +------+--------+------+--------+-------+
    |    2 | Naruto |   18 | A      |   960 |
    |    5 | Nami   |   17 | B      |   896 |
    +------+--------+------+--------+-------+

    Query: Display those students records whose name ends with i:

    SELECT *
    FROM students
    WHERE name LIKE '%i';
    

    Output

    +------+-------+------+--------+-------+
    | id   | name  | age  | grades | marks |
    +------+-------+------+--------+-------+
    |    4 | Sanji |   21 | B      |   899 |
    |    5 | Nami  |   17 | B      |   896 |
    +------+-------+------+--------+-------+
    

    Query: Display those students records whose has the letter r in their name

    SELECT *
    FROM students
    WHERE name LIKE '%r%';
    

    Output

    +------+--------+------+--------+-------+
    | id   | name   | age  | grades | marks |
    +------+--------+------+--------+-------+
    |    2 | Naruto |   18 | A      |   960 |
    |    3 | Zoro   |   20 | A      |   940 |
    |    6 | Robin  | NULL | B      |   860 |
    +------+--------+------+--------+-------+
    

    Query: Display those students records whose name second letter is a

    SELECT *
    FROM students
    WHERE name LIKE '_a%';
    

    Output

    +------+--------+------+--------+-------+
    | id   | name   | age  | grades | marks |
    +------+--------+------+--------+-------+
    |    2 | Naruto |   18 | A      |   960 |
    |    4 | Sanji  |   21 | B      |   899 |
    |    5 | Nami   |   17 | B      |   896 |
    +------+--------+------+--------+-------+
    

    Query: Display those students records whose name start with N and their name length must be greater than 4.

    SELECT *
    FROM students
    WHERE name LIKE 'n____%';
    

    Output

    +------+--------+------+--------+-------+
    | id   | name   | age  | grades | marks |
    +------+--------+------+--------+-------+
    |    2 | Naruto |   18 | A      |   960 |
    +------+--------+------+--------+-------+
    

    Summary

    • LIKE operator is used to search for the matching pattern from the data set.
    • To create a pattern we have two LIKE wildcards %(percentage) and _(Underscore)
    • % represents zero or more than zero characters
    • _ represent a single character.
    • We always use LIKE operator with the WHERE clause.

    People are also reading: