SQL wildcards
With SQL wildcards, you can create search patterns that can be compared against your data. The wildcards themselves are actually characters that have special meanings within the SQL WHERE clauses.
When searching for partial string matches, you might be interested in:
- Strings that begins or ends with a certain character
- Strings that begins or ends with a substring
- Strings containing a certain character anywhere within the string
- Strings containing a substring anywhere within the string
- Strings with a specific format, regardless of individual characters
The following wildcards are available in SQL:
- % – matches any string of zero or more characters
- _ (underscore) – matches a single character
Here is a demo table:
+-----------------+-------------+-------------------+--------------+
| LAST NAME | FIRST NAME | CITY | COUNTRY |
+-----------------+-------------+-------------------+--------------+
| Schmitt | Carine | Nantes | France |
| King | Jean | Las Vegas | USA |
| Ferguson | Peter | Melbourne | Australia |
| Labrune | Janine | Nantes | France |
| Bergulfsen | Jonas | Stavern | Norway |
| Nelson | Susan | San Rafael | USA |
| Piestrzeniewicz | Zbyszek | Warszawa | Poland |
| Keitel | Roland | Frankfurt | Germany |
| Murphy | Julie | San Francisco | USA |
| Lee | Kwai | NYC | USA |
| Freyre | Diego | Madrid | Spain |
| Berglund | Christina | Lule? | Sweden |
| Petersen | Jytte | Kobenhavn | Denmark |
| Saveley | Mary | Lyon | France |
| Natividad | Eric | Singapore | Singapore |
| Young | Jeff | NYC | USA |
| Leong | Kelvin | Allentown | USA |
| Hashimoto | Juri | Burlingame | USA |
| Victorino | Wendy | Singapore | Singapore |
| Oeztan | Veysel | Bergen | Norway |
| Franco | Keith | New Haven | USA |
| de Castro | Isabel | Lisboa | Portugal |
| Rancé | Martine | Lille | France |
| Bertrand | Marie | Paris | France |
| Tseng | Jerry | Cambridge | USA |
| King | Julie | Bridgewater | USA |
| Kentary | Mory | Kita-ku | Japan |
| Frick | Michael | NYC | USA |
| Karttunen | Matti | Helsinki | Finland |
| Ashworth | Rachel | Manchester | UK |
| Cassidy | Dean | Dublin | Ireland |
| Taylor | Leslie | Brickhaven | USA |
| Devon | Elizabeth | Liverpool | UK |
| Tamuri | Yoshi | Vancouver | Canada |
| Barajas | Miguel | Brickhaven | USA |
| Young | Julie | Pasadena | USA |
| Walker | Brydey | Singapore | Singapore |
| Citeaux | Frédérique | Strasbourg | France |
| Gao | Mike | Central Hong Kong | Hong Kong |
| Saavedra | Eduardo | Barcelona | Spain |
| Young | Mary | Glendale | USA |
| Kloss | Horst | Cunewalde | Germany |
| Ibsen | Palle | ?rhus | Denmark |
| Fresni?re | Jean | Montréal | Canada |
| Camino | Alejandra | Madrid | Spain |
| Thompson | Valarie | San Diego | USA |
| Bennett | Helen | Cowes | UK |
| Roulet | Annette | Toulouse | France |
| Messner | Renate | Frankfurt | Germany |
| Accorti | Paolo | Torino | Italy |
| Da Silva | Daniel | Paris | France |
| Tonini | Daniel | Versailles | France |
| Pfalzheim | Henriette | Köln | Germany |
| Lincoln | Elizabeth | Tsawassen | Canada |
| Franken | Peter | München | Germany |
| O'Hara | Anna | North Sydney | Australia |
| Rovelli | Giovanni | Bergamo | Italy |
| Huxley | Adrian | Chatswood | Australia |
| Hernandez | Marta | Cambridge | USA |
| Harrison | Ed | Fribourg | Switzerland |
| Holz | Mihael | Gen?ve | Switzerland |
| Klaeboe | Jan | Oslo | Norway |
| Schuyler | Bradley | Amsterdam | Netherlands |
| Andersen | Mel | Berlin | Germany |
| Koskitalo | Pirkko | Oulu | Finland |
| Dewey | Catherine | Bruxelles | Belgium |
| Frick | Steve | White Plains | USA |
| Huang | Wing | New Bedford | USA |
| Brown | Julie | San Francisco | USA |
| Graham | Mike | Auckland | New Zealand |
| Brown | Ann | London | UK |
| Brown | William | Newark | USA |
| Calaghan | Ben | South Brisbane | Australia |
| Suominen | Kalle | Espoo | Finland |
| Cramer | Philip | Brandenburg | Germany |
| Cervantes | Francisca | Philadelphia | USA |
| Fernandez | Jesus | Madrid | Spain |
| Chandler | Brian | Los Angeles | USA |
| McKenna | Patricia | Cork | Ireland |
| Lebihan | Laurence | Marseille | France |
| Henriot | Paul | Reims | France |
| Kuger | Armand | Hatfield | South Africa |
| MacKinlay | Wales | Auckland | New Zealand |
| Josephs | Karin | Münster | Germany |
| Yoshido | Juri | Boston | USA |
| Young | Dorothy | Nashua | USA |
| Rodriguez | Lino | Lisboa | Portugal |
| Urs | Braun | Bern | Switzerland |
| Nelson | Allen | Brickhaven | USA |
| Cartrain | Pascale | Charleroi | Belgium |
| Pipps | Georg | Salzburg | Austria |
| Cruz | Arnold | Makati City | Philippines |
| Moroni | Maurizio | Reggio Emilia | Italy |
| Shimamura | Akiko | Minato-ku | Japan |
| Perrier | Dominique | Paris | France |
| Müller | Rita | Stuttgart | Germany |
| McRoy | Sarah | Wellington | New Zealand |
| Donnermeyer | Michael | Munich | Germany |
| Hernandez | Maria | NYC | USA |
| Feuer | Alexander | Leipzig | Germany |
| Lewis | Dan | Glendale | USA |
| Larsson | Martha | Bräcke | Sweden |
| Frick | Sue | San Jose | USA |
| Mendel | Roland | Graz | Austria |
| Murphy | Leslie | New Haven | USA |
| Choi | Yu | NYC | USA |
| Sommer | Martín | Madrid | Spain |
| Ottlieb | Sven | Aachen | Germany |
| Benitez | Violeta | New Bedford | USA |
| Anton | Carmen | Madrid | Spain |
| Clenahan | Sean | Glen Waverly | Australia |
| Ricotti | Franco | Milan | Italy |
| Thompson | Steve | Burbank | USA |
| Moos | Hanna | Mannheim | Germany |
| Semenov | Alexander | Saint Petersburg | Russia |
| Altagar,G M | Raanan | Herzlia | Israel |
| Roel | José Pedro | Sevilla | Spain |
| Salazar | Rosa | Philadelphia | USA |
| Taylor | Sue | Brisbane | USA |
| Smith | Thomas | London | UK |
| Franco | Valarie | Boston | USA |
| Snowden | Tony | Auckland | New Zealand |
+-----------------+-------------+-------------------+--------------+
The following SQL statement will select all customers from a country starting with “Ger”:
select * from customers WHERE Country LIKE ‘Ger%’;
This would produce the following result:
+-------------+------------+-------------+---------+-
| LAST NAME | FIRST NAME | CITY | COUNTRY |
+-------------+------------+-------------+---------+-
| Keitel | Roland | Frankfurt | Germany |
| Kloss | Horst | Cunewalde | Germany |
| Messner | Renate | Frankfurt | Germany |
| Pfalzheim | Henriette | Köln | Germany |
| Franken | Peter | München | Germany |
| Andersen | Mel | Berlin | Germany |
| Cramer | Philip | Brandenburg | Germany |
| Josephs | Karin | Münster | Germany |
| Müller | Rita | Stuttgart | Germany |
| Donnermeyer | Michael | Munich | Germany |
| Feuer | Alexander | Leipzig | Germany |
| Ottlieb | Sven | Aachen | Germany |
| Moos | Hanna | Mannheim | Germany |
+-------------+------------+-------------+---------+-