[ ] (Wildcard - Character(s) to Match) (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
SQL analytics endpoint in Microsoft Fabric
Warehouse in Microsoft Fabric
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
.
Examples
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.
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
C: Using a set that combines ranges and single characters
A wildcard set can include both single characters and ranges. The following example uses the [] operator to find a string that begins with a number or a series of special characters.
SELECT [object_id], OBJECT_NAME(object_id) AS [object_name], name, column_id
FROM sys.columns
WHERE name LIKE '[0-9!@#$.,;_]%';
Here is the result set.
object_id object_name name column_id
--------- ----------- ---- ---------
615673241 vSalesPersonSalesByFiscalYears 2002 5
615673241 vSalesPersonSalesByFiscalYears 2003 6
615673241 vSalesPersonSalesByFiscalYears 2004 7
1591676718 JunkTable _xyz 1
See Also
LIKE (Transact-SQL)
PATINDEX (Transact-SQL)
% (Wildcard - Character(s) to Match) (Transact-SQL)
[^] (Wildcard - Character(s) Not to Match) (Transact-SQL)
_ (Wildcard - Match One Character) (Transact-SQL)