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.