How to use Wildcard Characters in LIKE Operations in SQL Server

Using wildcard characters with the LIKE operator makes pattern matching more flexible, because wildcard characters can be matched with a specified pattern of characters as needed. Wildcard characters in SQL Server are:

- Percent %
- Underscore _
- Brackets []
- Caret [^]

The Percent Wildcard Character (%):

The Percent Wildcard Character represents any string of zero or more characters.

For example, to find all employees with the first name starting with 'J' in the Person.Person table, use this query:

SELECT FirstName, MiddleName, LastName
FROM Person.Person
WHERE FirstName LIKE 'J%'

like_operator_percent_wildcard_1

To find all employees that have letter 'c' inside the first name:

SELECT FirstName, MiddleName, LastName
FROM Person.Person
WHERE FirstName LIKE '%c%'

like_operator_percent_wildcard_2

If your SQL Server has a case insensitive collation (CI), names that have letter 'C' or 'c' are returned.

The Underscore Wildcard Character (_):

The Underscore Wildcard Character represents any single character.

To find all employees with the first name that is 3 letters long and is starting with 'Jo':

SELECT FirstName, MiddleName, LastName
FROM Person.Person
WHERE FirstName LIKE 'Jo_'

like_operator_underscore_wildcard_1

Bracketed Characters []:

Brackets are used to search for any single character within the specified range [a-c] or set [abc].

To find all employees with a 3 characters long first name that begins with any single character between 'J' and 'Z' and ends with 'oe':

SELECT FirstName, MiddleName, LastName
FROM Person.Person
WHERE FirstName LIKE '[J-Z]oe'

like_operator_bracket_wildcard_1

To find all employees with a 3 characters long first name that begins with characters between 'J' or 'R' and ends with 'oy':

SELECT FirstName, MiddleName, LastName
FROM Person.Person
WHERE FirstName LIKE '[JR]oy'

like_operator_bracket_wildcard_2

The Caret Wildcard Character [^]:

The Caret Wildcard Character is used to search for any single character not within the specified range [^a-c] or set [^abc].

To find all employees with a 3 characters long first name that begins with 'Ja' and the third character is not 'n':

SELECT FirstName, MiddleName, LastName
FROM Person.Person
WHERE FirstName LIKE 'Ja[^n]'

like_operator_caret_wildcard_1

If the post helped you, please share it:
Pin It

1 comment to How to use Wildcard Characters in LIKE Operations in SQL Server

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">