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

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric

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.

SQL

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

Here's the result set.

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.

SQL

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

Here's the result set.

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)