T Sql 简明教程

T-SQL - LIKE Clause

MS SQL Server LIKE 子句用于使用通配符运算符将值与类似值进行比较。与 LIKE 运算符结合使用的通配符有两种 -

The MS SQL Server LIKE clause is used to compare a value to similar values using wildcard operators. There are two wildcards used in conjunction with the LIKE operator −

The percent sign (%)
The underscore (_)

百分号表示零个、一个或多个字符。下划线表示单个数字或字符。这些符号可以组合使用。

The percent sign represents zero, one, or multiple characters. The underscore represents a single number or character. The symbols can be used in combinations.

Syntax

以下是 % 和 _ 的基本语法。

Following is the basic syntax of % and _.

SELECT *\column-list FROM table_name
WHERE column LIKE 'XXXX%'

or

SELECT *\column-list FROM table_name
WHERE column LIKE '%XXXX%'

or

SELECT *\column-list FROM table_name
WHERE column LIKE 'XXXX_'

or

SELECT *\column-list FROM table_name
WHERE column LIKE '_XXXX'

or

SELECT  *\column-list FROM table_name
WHERE column LIKE '_XXXX_'

您可以使用 AND 或 OR 运算符组合 N 个条件。XXXX 可以是任何数字或字符串值。

You can combine N number of conditions using AND or OR operators. XXXX could be any numeric or string value.

Example

以下是一些示例，显示 WHERE 部分具有带 '%' 和 '_' 运算符的不同 LIKE 子句。

Following are a number of examples showing WHERE part having different LIKE clause with '%' and '_' operators.

Sr.No	Statement & Description
1	WHERE SALARY LIKE '200%' Finds any values that start with 200
2	WHERE SALARY LIKE '%200%' Finds any values that have 200 in any position
3	WHERE SALARY LIKE '_00%' Finds any values that have 00 in the second and third positions
4	WHERE SALARY LIKE '2_%_%' Finds any values that start with 2 and are at least 3 characters in length
5	WHERE SALARY LIKE '%2' Finds any values that end with 2
6	WHERE SALARY LIKE '_2%3' Finds any values that have a 2 in the second position and end with a 3
7	WHERE SALARY LIKE '2___3' Finds any values in a five-digit number that start with 2 and end with 3

可以将 CUSTOMERS 表视为包含以下记录。

Consider the CUSTOMERS table having the following records.

ID  NAME       AGE       ADDRESS            SALARY
1   Ramesh     32        Ahmedabad          2000.00
2   Khilan     25        Delhi              1500.00
3   kaushik    23        Kota               2000.00
4   Chaitali   25        Mumbai             6500.00
5   Hardik     27        Bhopal             8500.00
6   Komal      22        MP                 4500.00
7   Muffy      24        Indore             10000.00

以下命令是一个示例，它会显示 CUSTOMERS 表中 SALARY 以 200 开头的所有记录。

Following command is an example, which will display all the records from CUSTOMERS table where SALARY starts with 200.

SELECT * FROM CUSTOMERS
WHERE SALARY LIKE '200%';

以上命令将生成以下输出。

The above command will produce the following output.

ID   NAME     AGE     ADDRESS       SALARY
1    Ramesh   32      Ahmedabad     2000.00
3    kaushik  23      Kota          2000.00