(Wildcard - Character(s) to Match) (Transact-SQL)

 

Updated: December 6, 2016

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Matches any single character within the specified range or set that is specified between brackets [ ]. These wildcard characters can be used in string comparisons that involve pattern matching, such as LIKE and PATINDEX.

A: Simple example

The following example returns the names of that start with the letter m. [n-z] specifies that the second letter must be somewhere in the range from n to z. The percent wildcard % allows any or no characters starting with the 3 character. The model and msdb databases meet this criteria. The master database does not and is excluded from the result set.

SELECT name FROM sys.databases
WHERE name LIKE 'm[n-z]%';

Here is the result set.

name
-----
model
msdb

You may have additional qualifying databases installed.

B: More complex example

The following example uses the [] operator to find the IDs and names of all Adventure Works employees who have addresses with a four-digit postal code.

-- Uses AdventureWorks  
  
SELECT e.BusinessEntityID, p.FirstName, p.LastName, a.PostalCode  
FROM HumanResources.Employee AS e  
INNER JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID  
INNER JOIN Person.BusinessEntityAddress AS ea ON e.BusinessEntityID = ea.BusinessEntityID  
INNER JOIN Person.Address AS a ON a.AddressID = ea.AddressID  
WHERE a.PostalCode LIKE '[0-9][0-9][0-9][0-9]';  

Here is the result set:

EmployeeID      FirstName      LastName      PostalCode  
----------      ---------      ---------     ----------  
290             Lynn           Tsoflias      3000  

LIKE (Transact-SQL)
PATINDEX (Transact-SQL)
% (Wildcard - Character(s) to Match)
[^] (Wildcard - Character(s) Not to Match)
_ (Wildcard - Match One Character)

Community Additions

ADD
Show: