SQL Wildcards |
SQL Sample Code - SQL Wildcards |
| Wildcard | Description |
| % | (Percentage Sign) matches zero or more characters |
| _ | (Underscore) matches a single character |
Notes:- Wildcards are used a lot using the LIKE operator. Go to the LIKE section to learn more.
See the SQL samples section below for an explanation of Wildcards.
What do SQL Wildcards do? |
The SQL LIKE operator filters out columns based upon criteria specified with conditions which match a certain pattern.
In SQL, wildcard characters can be used in "LIKE" expressions; the percent sign (%) matches zero or more characters, and underscore (_) a single character.
Sample 1 – % (Percentage) Wildcard |
In this SQL sample we will use the database table ‘tblCompany’.
| Company_ID | CompanyName | Address | Town |
| 1 | SQL Sample | 1 Sample St | Hamburg |
| 2 | SQL Code Land | 2 Code Rd | Hamburg |
| 3 | Sample Code World | 66 SQL St | Curry |
| 4 | SQL Reference Ltd | 34 Reference St | Pisa |
| 5 | MySQL Code Geeks | 5 Sample Ave | Humberside |
In this SQL sample we want to retrieve all the Columns from the tblCompany table whose company has a Town beginning with ‘H’.
Use the SELECT statement below:
| SELECT * FROM tblCompany WHERE Town LIKE ‘H%’; |
The table of results will look like this:
Notes:- Wildcards are used a lot using the LIKE operator. Go to the LIKE section to learn more.
| Company_ID | CompanyName | Address | Town |
| 1 | SQL Sample | 1 Sample St | Hamburg |
| 2 | SQL Code Land | 2 Code Rd | Hamburg |
| 5 | MySQL Code Geeks | 5 Sample Ave | Humberside |
Sample 2 – % (Percentage) Wildcard |
In this SQL sample we will use the database table ‘tblCompany’.
| Company_ID | CompanyName | Address | Town |
| 1 | SQL Sample | 1 Sample St | Hamburg |
| 2 | SQL Code Land | 2 Code Rd | Hamburg |
| 3 | Sample Code World | 66 SQL St | Curry |
| 4 | SQL Reference Ltd | 34 Reference St | Pisa |
| 5 | MySQL Code Geeks | 5 Sample Ave | Humberside |
In this example we want to retrieve all the Columns from the tblCompany table whose company Town has a ‘ur’ in it.
Use the SELECT statement below:
| SELECT * FROM tblCompany WHERE Address LIKE ‘%ur%’; |
The table of results will look like this:
Notes:- Wildcards are used a lot using the LIKE operator. Go to the LIKE section to learn more.
| Company_ID | CompanyName | Address | Town |
| 1 | SQL Sample | 1 Sample St | Hamburg |
| 2 | SQL Code Land | 2 Code Rd | Hamburg |
| 3 | Sample Code World | 66 SQL St | Curry |
Sample 3 – _ (Underscore) Wildcard |
In this SQL sample we will use the database table ‘tblCompany’.
| Company_ID | CompanyName | Address | Town |
| 1 | SQL Sample | 1 Sample St | Hamburg |
| 2 | SQL Code Land | 2 Code Rd | Hamburg |
| 3 | Sample Code World | 66 SQL St | Curry |
| 4 | SQL Reference Ltd | 34 Reference St | Pisa |
| 5 | MySQL Code Geeks | 5 Sample Ave | Humberside |
In this SQL sample we want to retrieve all the Columns from the tblCompany table whose company Town that starts with H, any second letter followed by an ‘mb’, any 5th letter and ends with ‘rg’.
Use the SELECT statement below:
| SELECT * FROM tblCompany WHERE Town LIKE ‘H_mb%’; |
The table of results will look like this:
| Company_ID | CompanyName | Address | Town |
| 1 | SQL Sample | 1 Sample St | Hamburg |
| 2 | SQL Code Land | 2 Code Rd | Hamburg |
| 5 | MySQL Code Geeks | 5 Sample Ave | Humberside |
Sample 4 – Combining _ and % Wildcards |
In this SQL sample we will use the database table ‘tblCompany’.
| Company_ID | CompanyName | Address | Town |
| 1 | SQL Sample | 1 Sample St | Hamburg |
| 2 | SQL Code Land | 2 Code Rd | Hamburg |
| 3 | Sample Code World | 66 SQL St | Curry |
| 4 | SQL Reference Ltd | 34 Reference St | Pisa |
| 5 | MySQL Code Geeks | 5 Sample Ave | Humberside |
In this SQL sample we want to retrieve all the Columns from the tblCompany table whose company Town that starts with H has any second letter, then contains ‘mb’ and anything after that.
Use the SELECT statement below:
| SELECT * FROM tblCompany WHERE Town LIKE ‘H_mb%’; |
The table of results will look like this:
| Company_ID | CompanyName | Address | Town |
| 1 | SQL Sample | 1 Sample St | Hamburg |
| 2 | SQL Code Land | 2 Code Rd | Hamburg |
Related SQL Sample Code:
|
|