Use the underscore character _ to match any single character in a string comparison operation that involves pattern matching, such as LIKE
and PATINDEX
.
The following example returns all database names that begin with the letter m
and have the letter d
as the third letter. The underscore character specifies that the second character of the name can be any letter. The model
and msdb
databases meet this criteria. The master
database does not.
Here is the result set.
name
-----
model
msdb
You may have additional databases that meet this criteria.
You can use multiple underscores to represent multiple characters. Changing the LIKE
criteria to include two underscores 'm__%
includes the master database in the result.
The following example uses the _ operator to find all the people in the Person
table, who have a three-letter first name that ends in an
.
-- USE AdventureWorks2012
SELECT FirstName, LastName
FROM Person.Person
WHERE FirstName LIKE '_an'
ORDER BY FirstName;
The following example returns the names of the fixed database roles like db_owner
and db_ddladmin
, but it also returns the dbo
user.
The underscore in the third character position is taken as a wildcard, and is not filtering for only principals starting with the letters db_
. To escape the underscore enclose it in brackets [_]
.
Now the dbo
user is excluded.
Here is the result set.
name
-------------
db_owner
db_accessadmin
db_securityadmin
...
LIKE (Transact-SQL)
PATINDEX (Transact-SQL)
% (Wildcard - Character(s) to Match)
[ ] (Wildcard - Character(s) to Match)
[^] (Wildcard - Character(s) Not to Match)