Edit

Share via

[^] (Wildcard - characters not to match) (Transact-SQL)

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

Matches any single character that isn't 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

The code samples in this article use the AdventureWorks2022 or AdventureWorksDW2022 sample database, which you can download from the Microsoft SQL Server Samples and Community Projects home page.

A: Basic example

The following example uses the [^] operator to find the top five people in the Contact table who have a first name that starts with Al and has a third letter that isn't the letter a.

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 doesn't 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's the result set.

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

Related content