[^] (Wildcard - Character(s) Not to Match) (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
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]%';
Here is 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.
SELECT [object_id], OBJECT_NAME(object_id) AS [object_name], name, column_id
FROM sys.columns
WHERE name LIKE '[^0-9A-z]%';
Here is 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)