SQL LIKE Operator

SQL LIKE operator is used with WHERE clause to matches specific pattern in a column. SQL LIKE condition apply on table column data.

Following two wildcards are often used with the LIKE operator

  • % - Represents zero, or any number of characters
  • _ - Represents a single character

Syntax

Considering following SQL LIKE condition syntax,

SELECT * FROM table_name
    WHERE column_name LIKE 'pattern';

Pattern write inside opening or closing single delimit characters (''). Do not use double delimit characters ("") because double delimit use as delimiter identifier.

...WHERE column_name LIKE 'pattern';   -- Correct way
...WHERE column_name LIKE "pattern";   -- Incorrect way

SQL Wildcards Characters

SQL Wildcards characters used for searching string pattern. SQL Wildcard character use with LIKE condition for searching pattern string from database table.

Following Wildcard characters pattern use for certain type expression,

Wildcard Description
_
(underscore sign)
Underscore sign matches any exactly single character with in string.
Example. 'Op_l Kole', '_pal Kole', 'Opal Kol_'
%
(Percentage sign)
Percentage sign matches any number of characters (0 or more characters).
Example. 'Op%', '%Kole', 'Opal%'

SQL LIKE Example (with WILDCARD)

Considering following user_info table is our example table.

SQL> SELECT * FROM users_info;

 NO NAME                  ADDRESS                         CONTACT_NO
--- --------------------- ------------------------------- ---------------
  1 Opal Kole             63 street Ct.                   000-444-7847
  2 Max Miller            41 NEW ROAD.                    000-444-8292
  3 Beccaa Moss           2500 green city.                000-444-7586
  4 Paul Singh            1343 Prospect St                000-444-7585
  5 Ken Myer              137 Clay Road                   000-444-7528
  6 Jack Evans            1365 Grove Way                  000-444-8401
  7 Reed Koch             1274 West Street                000-444-5228
  8 Gabe Hee              1220 Dallas Drive               000-444-5028
  9 Ben Mares             101 Candy Road                  000-444-5928
 10 Sariya Vargas         145 Taxo court.                 000-444-7454

10 rows selected.

LIKE with _ WILDCARD Character Example

SQL LIKE condition with _ (underscore) WILDCARD character to matches any exactly single character with in string.

SQL> SELECT * FROM users_info WHERE name LIKE 'Pa_l S__gh';

 NO NAME                  ADDRESS                         CONTACT_NO
--- --------------------- ------------------------------- ---------------
  4 Paul Singh            1343 Prospect St                000-444-7585

1 rows selected.

LIKE with % WILDCARD Character Example

SQL LIKE condition with % (percentage) WILDCARD character to matches any number of characters.

SQL> SELECT * FROM users_info WHERE name LIKE 'Paul%';

 NO NAME                  ADDRESS                         CONTACT_NO
--- --------------------- ------------------------------- ---------------
  4 Paul Singh            1343 Prospect St                000-444-7585

1 rows selected.

LIKE (_ WILDCARD Character) with NOT condition Example

SQL LIKE (_ WILDCARD character) with NOT condition to return all record only exclude pattern result.

SQL> SELECT * FROM users_info WHERE name NOT LIKE 'Pa_l S__gh';

 NO NAME                  ADDRESS                         CONTACT_NO
--- --------------------- ------------------------------- ---------------
  1 Opal Kole             63 street Ct.                   000-444-7847
  2 Max Miller            41 NEW ROAD.                    000-444-8292
  3 Beccaa Moss           2500 green city.                000-444-7586
  5 Ken Myer              137 Clay Road                   000-444-7528
  6 Jack Evans            1365 Grove Way                  000-444-8401
  7 Reed Koch             1274 West Street                000-444-5228
  8 Gabe Hee              1220 Dallas Drive               000-444-5028
  9 Ben Mares             101 Candy Road                  000-444-5928
 10 Sariya Vargas         145 Taxo court.                 000-444-7454

9 rows selected.

LIKE (% WILDCARD Character) with NOT condition Example

SQL LIKE (% WILDCARD character) with NOT condition to return all record only exclude pattern result.

SQL> SELECT * FROM users_info WHERE name NOT LIKE 'Paul%';

 NO NAME                  ADDRESS                         CONTACT_NO
--- --------------------- ------------------------------- ---------------
  1 Opal Kole             63 street Ct.                   000-444-7847
  2 Max Miller            41 NEW ROAD.                    000-444-8292
  3 Beccaa Moss           2500 green city.                000-444-7586
  5 Ken Myer              137 Clay Road                   000-444-7528
  6 Jack Evans            1365 Grove Way                  000-444-8401
  7 Reed Koch             1274 West Street                000-444-5228
  8 Gabe Hee              1220 Dallas Drive               000-444-5028
  9 Ben Mares             101 Candy Road                  000-444-5928
 10 Sariya Vargas         145 Taxo court.                 000-444-7454

9 rows selected.