SQL Wildcard Characters

    The wildcard characters are used with SQL LIKE operator, and they are used to make a pattern which could find the perfect or resemblance match from the String data set.

    SQL Wildcard Characters

    The LIKE statement is used along with the WHERE clause and it set a match pattern condition using wildcard characters, and there are various wildcard characters which set different meaning to the set patterns. The Wildcard characters symbols can vary from one RDBMS to another.

    MS Access Wildcard Characters

    Wildcard character symbols Description Pattern Matching data
    * Zero or more characters pa* pay, par, pan, party, pays, padi, pals, etc.
    ? A single character p?y Pay, pby, pcy, pdy,….pzy
    [] Any single character from the bracket p[ae]y pay, and pey
    ! Not in the brackets p[!ea]y Match any 3 latter words which start with p and end with y, except pey and pay.
    - A range of characters p[a-g]y pay, pby, pcy, pdy, pey, pfy, and pgy
    # A single numeric character p#y p0y, p1y, p2y……….p9y.

    SQL server, and MySQL WildCard characters symbols

    Wildcard character symbols Description Pattern Matching data
    % Zero or more characters pa% pay, par, pan, party, pays, padi, pals, etc.
    _ A single character p_y Pay, pby, pcy, pdy,….pzy
    [] Any single character from the bracket p[ae]y pay, and pey
    ^ Not in the brackets p[^ea]y Match any 3 latter words which start with p and end with y, except pey and pay.
    - A range of characters p[a-g]y pay, pby, pcy, pdy, pey, pfy, and pgy

    Example For the example consider these sample table Students: 

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

    Query: Show those students name and age, whose name has exactly 4 letters. 

    SELECT name, age
FROM students
WHERE name LIKE "____";

    Output 

    +------+------+
| name | age  |
+------+------+
| Zoro |   20 |
| Nami |   17 |
| Goku |   27 |
+------+------+

    Summary

    • Wildcards are used to substitute one or more characters in a string.
    • It used along with LIKE Clause.

    People are also reading:

    Previous Tutorial Next Tutorial