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

title	[^] Wildcard to exclude characters
description	T-SQL wildcard for characters not to match
titleSuffix	SQL Server (Transact-SQL)
ms.custom	seo-lt-2019
ms.date	12/06/2016
ms.prod	sql
ms.prod_service	database-engine, sql-database
ms.reviewer
ms.technology	t-sql
ms.topic	language-reference
f1_keywords	wildcard [^]_TSQL [^] Not Match
dev_langs	TSQL
helpviewer_keywords	wildcard characters [SQL Server] [^] (wildcard - character(s) not to match)
ms.assetid	b970038f-f4e7-4a5d-96f6-51e3248c6aef
author	rothja
ms.author	jroth

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

[!INCLUDEtsql-appliesto-ss2008-asdb-xxxx-xxx_md]

Matches any single character that is not within the range or set specified between the square 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 uses the [^] operator to find the top 5 people in the Contact table who have a first name that starts with Al and has a third letter that is not the letter a.

-- Uses AdventureWorks  
  
SELECT TOP 5 FirstName, LastName  
FROM Person.Person  
WHERE FirstName LIKE 'Al[^a]%';  

[!INCLUDEssResult_md]

FirstName     LastName
---------     --------
Alex          Adams
Alexandra     Adams
Allison       Adams
Alisha        Alan
Alexandra     Alexander

B: Searching for ranges of characters

A wildcard set can include single characters or ranges of characters as well as combinations of characters and ranges. The following example uses the [^] operator to find a string that does not begin with a letter or number.

SELECT [object_id], OBJECT_NAME(object_id) AS [object_name], name, column_id 
FROM sys.columns 
WHERE name LIKE '[^0-9A-z]%';

[!INCLUDEssResult_md]

object_id     object_name   name    column_id
---------     -----------   ----    ---------
1591676718    JunkTable     _xyz    1

See Also

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