Geek University

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 |
+-------------+------------+-------------+---------+-

Subscribe to our Newsletter

Like us on Facebook

Share on Twitter

top
Developed by Signum Soft