SQL - Wildcards


Advertisements

Previous Page Next Page

The SQL wildcard character is used to specify one or more character in a string. It is used with SQL LIKE clause and SQL WHERE clause to search for a specified pattern.

SymbolDescriptionExample
%Represents zero, one or multiple characters.
Note: In MS Access, asterisk (*) is used instead of percentage sign (%).
'J%' represents a value that start with "J", for example - John, Jo and Jack etc.
_Represents one character.
Note: In MS Access, question mark (?) is used instead of (_).
'_o%' represents a value that have "o" in the second position, for example - John, Jo and Journey etc.
[]Represents any single character specified within [].'[ack]%' represents a value that starts with a, c or k, for example - ant, cat or kite etc.
-Represents a range of characters.'[a-d]%' represents a value that starts with a, b, c or d, for example - ant, bat, cat or dog etc.
^Represents any character not specified within [].
Note: In MS Access, exclamation mark (!) is used instead of (^).
'[^ack]%' represents a value that does not start with a, c or k, for example - bat, dog or rat etc.
#In MS Access, represents any single numeric character.'5#5' represents a three digit number with 5 at one's and hundred's place, for example - 505, 555 or 595 etc.

The below table describes patterns which is used with LIKE operator and uses (%) and (_).

PatternDescription
'J%'A value that start with "J".
'%n'A value that end with "n".
'%oh%'A value that have "oh" in any position.
'_o%'A value that have "o" in the second position.
'J_%'A value that start with "J" and have at least 2 characters.
'J%n'A value that start with "J" and ends with "n".

Example:

Consider a database tables called Employee with the following records:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

  • Using the % Wildcard : To select all records of the Employee table with Name starting with 'Jo', the SQL code is given below.

    SELECT * FROM Employee
    WHERE Name LIKE 'Jo%';
    

    This will produce the result as shown below:

    EmpIDNameCityAgeSalary
    1JohnLondon253000
    3JoParis272800

  • Using the _ Wildcard : To select all records of the Employee table with Name starting with 'o' as second character, the SQL code is mentioned below.

    SELECT * FROM Employee
    WHERE Name LIKE '_o%';
    

    The result of the above code will be:

    EmpIDNameCityAgeSalary
    1JohnLondon253000
    3JoParis272800

  • Using the [] Wildcard : To select all records of the Employee table with Name starting with 'K', 'R' or 'M', the SQL code is:

    SELECT * FROM Employee
    WHERE Name LIKE '[KRM]%';
    

    The result of the code will be:

    EmpIDNameCityAgeSalary
    2MarryNew York242750
    4KimAmsterdam303100
    5RameshNew Delhi283000

  • Using the ^ Wildcard : To select all records of the Employee table with Name not starting with 'K', 'R' or 'M', the SQL code is:

    SELECT * FROM Employee
    WHERE Name LIKE '[^KRM]%';
    

    The result of the code will be:

    EmpIDNameCityAgeSalary
    1JohnLondon253000
    3JoParis272800
    6HuangBeijing282800

  • Using the - Wildcard : To select all records of the Employee table with Name not starting with 'G' to 'J', the SQL code is:

    SELECT * FROM Employee
    WHERE Name LIKE '[G-J]%';
    

    The result produced by the code will be following:

    EmpIDNameCityAgeSalary
    1JohnLondon253000
    3JoParis272800
    6HuangBeijing282800

Previous Page Next Page
Advertisements