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

APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) 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 names 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 doesn't meet the criteria and is excluded from the result set.

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

Here is the result set.


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]';  

Following is the result set:

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

See Also

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