wildcard-character-s-to-match-transact-sql.md

title	titleSuffix	description	author	ms.author	ms.date	ms.service	ms.subservice	ms.topic	ms.custom	f1_keywords	helpviewer_keywords	dev_langs	monikerRange
[ ] Wildcard to match characters	SQL Server (Transact-SQL)	Use a wildcard to match one or more characters.	rwestMSFT	randolphwest	12/06/2016	sql	t-sql	reference	ignite-2024	Match wildcard [ ] [_]_TSQL	wildcard characters [SQL Server] [ ] (wildcard - character(s) to match)	TSQL	>=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric

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

[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw-fabricsqldb]

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

[!INCLUDEssResult_md]

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 [!INCLUDEssSampleDBCoShort] 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]';  

[!INCLUDEssResult_md]

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!@#$.,;_]%';

[!INCLUDEssResult_md]

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)