（通配符 - 匹配一个字符）(Transact-SQL) (Wildcard - Match One Character) (Transact-SQL)

12/06/2016

适用于：Applies to: SQL ServerSQL Server（所有支持的版本）SQL ServerSQL Server (all supported versions) Azure SQL 数据库Azure SQL DatabaseAzure SQL 数据库Azure SQL Database适用于：Applies to: SQL ServerSQL Server（所有支持的版本）SQL ServerSQL Server (all supported versions) Azure SQL 数据库Azure SQL DatabaseAzure SQL 数据库Azure SQL Database

下划线字符 _ 用于匹配涉及模式匹配的字符串比较操作（如 LIKE 和 PATINDEX）中的任何单个字符。Use the underscore character _ to match any single character in a string comparison operation that involves pattern matching, such as LIKE and PATINDEX.

示例Examples

A：简单示例A: Simple example

以下示例返回以字母 m 开头且第三个字母为 d 的所有数据库名称。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. model 数据库和 msdb 数据库均符合此条件。The model and msdb databases meet this criteria. master 数据库则不符合。The master database does not.

SELECT name FROM sys.databases
WHERE name LIKE 'm_d%';

下面是结果集：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. 将 LIKE 条件更改为包含两个下划线 'm__% 会将 master 数据库包含在结果中。Changing the LIKE criteria to include two underscores 'm__% includes the master database in the result.

B：更复杂的示例B: More complex example

以下示例使用 _ 运算符在 Person 表中查找名字由三个字母组成且以 an 结尾的所有人。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;

C：对下划线字符进行转义C: Escaping the underscore character

以下示例会返回 db_owner、db_ddladmin 等固定数据库角色的名称，但它也会返回 dbo 用户。The following example returns the names of the fixed database roles like db_owner and db_ddladmin, but it also returns the dbo user.

SELECT name FROM sys.database_principals
WHERE name LIKE 'db_%';

第三个字符位置处的下划线被视为通配符，它无法仅筛选出以字母 db_ 开头的主体。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 [_].

SELECT name FROM sys.database_principals
WHERE name LIKE 'db[_]%';

现在已排除 dbo 用户。Now the dbo user is excluded.