sql 逻辑运算符_SQL Like逻辑运算符介绍和概述

sql 逻辑运算符

The SQL Like is a logical operator that is used to determine whether a specific character string matches a specified pattern. It is commonly used in a Where clause to search for a specified pattern in a column.

SQL Like是逻辑运算符，用于确定特定的字符串是否与指定的模式匹配。 通常在Where子句中使用它来搜索列中的指定模式。

This operator can be useful in cases when we need to perform pattern matching instead of equal or not equal. The SQL Like is used when we want to return the row if specific character string matches a specified pattern. The pattern can be a combination of regular characters and wildcard characters.

该运算符在我们需要执行模式匹配而不是相等或不相等的情况下很有用。 如果特定的字符串匹配指定的模式，当我们想返回该行时，将使用SQL Like。 该模式可以是常规字符和通配符的组合。

To return a row back, regular characters must exactly match the characters specified in the character string. The wildcard characters can be matched with arbitrary parts of the character string.

要返回行，常规字符必须与字符串中指定的字符完全匹配。 通配符可以与字符串的任意部分匹配。

Let’s use the AdventureWorks sample database and see some different SQL Like operators with ‘%’ and ‘_’ wildcards.

让我们使用AdventureWorks示例数据库，查看一些带有'％'和'_'通配符的不同SQL Like运算符。

使用％通配符（代表零个，一个或多个字符） (Using the % wildcard character (represents zero, one, or multiple characters))

The query below returns all telephone numbers that have area code “415” in the “PersonPhone” table:

下面的查询返回“ PersonPhone”表中所有区号为“ 415”的电话号码：

SELECT p.FirstName, p.LastName, ph.PhoneNumber  
FROM Person.PersonPhone AS ph  
INNER JOIN Person.Person AS p  
ON ph.BusinessEntityID = p.BusinessEntityID  
WHERE ph.PhoneNumber LIKE '415%'  
ORDER by p.LastName;  
GO

Notice that ‘415%’ symbol is specified in the Where clause. What this means is that SQL Server will search for the number 415 followed by any string of zero or more characters. Here is the result set:

请注意，在Where子句中指定了“ 415％”符号。 这意味着SQL Server将搜索数字415，后跟零个或多个字符的任何字符串。 这是结果集：

The Not logical operator reverses the value of any Boolean expression. So, if we just specify Not like with the ‘%’ wildcard character in SQL Like clause, add one additional condition and place it in the same statement as above we should get a query like this:

非逻辑运算符会反转任何布尔表达式的值。 因此，如果我们仅在SQL Like子句中使用“％”通配符指定“不喜欢”，则添加一个附加条件并将其放置在与上述相同的语句中，我们应该得到如下查询：

SELECT p.FirstName, p.LastName, ph.PhoneNumber  
FROM Person.PersonPhone AS ph  
INNER JOIN Person.Person AS p  
ON ph.BusinessEntityID = p.BusinessEntityID  
WHERE ph.PhoneNumber NOT LIKE '415%' AND p.FirstName = 'Gail'  
ORDER BY p.LastName;  
GO

This time, the query returned all the records in the “PersonPhone” table that have area codes other than 415:

这次，查询返回“ PersonPhone”表中所有区号不是415的记录：

Furthermore, let’s say that we want to find all the records where a name contains “ssa” in them. We can use the query below:

此外，假设我们要查找名称中包含“ ssa”的所有记录。 我们可以使用以下查询：

SELECT p.FirstName, p.LastName
FROM Person.Person AS p
WHERE LastName LIKE '%ssa%'
ORDER BY p.LastName;  
GO

Notice that by using ‘%’ before and after “ssa”, we are telling the SQL Server to find all records in which “Person.Person” has “ssa” characters and it doesn’t matter what other characters are before and after “ssa”:

请注意，通过在“ ssa”之前和之后使用“％”，我们告诉SQL Server查找“ Person.Person”具有“ ssa”字符的所有记录，而“ ssa”：

使用“ _”通配符（代表单个字符） (Using the “_” wildcard character (represents a single character))

The SQL Like underscore character e.g. „ is used when we want to check a single character that can be anything and provide the rest of the characters for our match.

SQL Like下划线字符（例如„）在我们要检查可以为任意字符的单个字符并为我们的匹配提供其余字符时使用。

Let’s say that if we want to return all records wherein the “FirstName” table first character can be anything but rest of them should be “en”. Use the query below:

假设我们要返回所有记录，其中“ FirstName”表的第一个字符可以是任何字符，但其余部分应为“ en”。 使用以下查询：

SELECT p.FirstName, 
       p.MiddleName
FROM Person.Person p
WHERE p.FirstName LIKE '_en';
GO

Here is the result set:

这是结果集：

Note that a combination of wildcards character can also be used at the end of the search pattern. For example, to return all telephone numbers that have an area code starting with 6 and ending in 2 in the “PersonPhone” table use the following query:

注意，通配符的组合也可以在搜索模式的末尾使用。 例如，要返回“ PersonPhone”表中区号以6开头并以2结尾的所有电话号码，请使用以下查询：

SELECT pp.PhoneNumber
FROM Person.PersonPhone pp
WHERE pp.PhoneNumber LIKE '6_2%'
GO

Note that the ‘%’ wildcard character is used after the underscore character since the area code is the first part of the phone number and additional characters exist after in the column value:

请注意，由于区号是电话号码的第一部分，并且在列值之后还有其他字符，因此在下划线字符后使用'％'通配符：

使用[]方括号（指定范围内的任何单个字符[at]或设置为[abc]） (Using the [ ] square brackets (any single character within the specified range [a-t] or set [abc]))

The SQL Like operator with square brackets is used when we want to have range. Let’s say if we want to find all the rows where “FirstName” first character start with [a-f]. Use the query below:

当我们想要范围时，使用带有方括号SQL Like运算符。 假设我们要查找“ FirstName”第一个字符以[af]开头的所有行。 使用以下查询：

SELECT BusinessEntityID, FirstName, LastName   
FROM Person.Person   
WHERE FirstName LIKE '[a-f]%';  
GO

As it can be seen we have used range [a-f]%. That means to return the first character from a to f and after that, any characters are fine because we used ‘%’ afterward:

可以看出，我们使用了[af]％的范围。 这意味着要将第一个字符从a返回到f，然后，任何字符都可以，因为之后我们使用了'％'：

To return any single character within a set use the example below to find employees on the “Person” table with the first name of Cheryl or Sheryl:

要返回集合中的任何单个字符，请使用以下示例在“人员”表上查找名字为Cheryl或Sheryl的员工：

SELECT BusinessEntityID, FirstName, LastName   
FROM Person.Person   
WHERE FirstName LIKE '[CS]heryl';  
GO

This query will return only Cheryl in this case, but it would have returned Sheryl as well if we had any records in the database:

在这种情况下，此查询将仅返回Cheryl，但是如果数据库中有任何记录，则该查询也将返回Sheryl：

Here’s another example when we actually have mixed results:

当我们实际上有混合结果时，这是另一个示例：

SELECT LastName, FirstName  
FROM Person.Person  
WHERE LastName LIKE 'Zh[ae]ng'  
ORDER BY LastName ASC, FirstName ASC;  
GO

The above query finds the records for employees in the “Person” table with last names of Zheng or Zhang:

上面的查询在“人员”表中查找姓郑或张的员工记录：

使用[^]方括号（不在指定范围内的任何单个字符[at]或设置为[abc]） (Using [^] square brackets (any single character not within the specified range [a-t] or set [abc]))

As you might have guessed, this is the opposite of the previous usage of the SQL Like operator with square brackets. Let’s say that we want to return all the records where “FirstName” first character does not start with [a to f]:

您可能已经猜到了，这与以前使用方括号SQL Like运算符相反。 假设我们要返回“ FirstName”首字符不是以[a至f]开头的所有记录：

SELECT BusinessEntityID, FirstName, LastName   
FROM Person.Person   
WHERE FirstName LIKE '[^a-f]%';  
GO

Notice that it only returned the records which do not start with any character from a to f:

请注意，它只返回了不以a到f开头的任何字符的记录：

With the set example, let’s say that we want to get all the records where “FirstName” does not start with a,d,j. We can use the query below:

以设置的示例为例，我们要获取“ FirstName”不是以a，d，j开头的所有记录。 我们可以使用以下查询：

SELECT BusinessEntityID, FirstName, LastName   
FROM Person.Person   
WHERE FirstName LIKE '[^adj]%';  
GO

Here is the result set:

这是结果集：

使用转义子句 (Using the escape clause)

This is one SQL Like predicate that is used to specify an escape character. The query below uses the Escape clause and the escape character:

这是一个SQL Like谓词，用于指定转义字符。 下面的查询使用Escape子句和转义字符：

USE tempdb;  
GO  
IF EXISTS(SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'mytbl2')
DROP TABLE mytbl2;  
GO  
USE tempdb;  
GO  
CREATE TABLE mytbl2(c1 SYSNAME);  
GO  
INSERT INTO mytbl2
VALUES('Discount is 10-15% off'), ('Discount is .10-.15 off');  
GO  
SELECT c1
FROM mytbl2
WHERE c1 LIKE '%10-15@% off%' ESCAPE '@';  
GO

It returns the exact character string 10-15% in column c1 of the mytbl2 table:

它在mytbl2表的c1列中返回准确的字符串10-15％：

I hope this article on the SQL Like operator has been informative and I thank you for reading.

我希望有关SQL Like运算符的这篇文章能为您提供丰富的信息，并感谢您的阅读。

翻译自: https://www.sqlshack.com/sql-like-logical-operator-introduction-and-overview/

sql 逻辑运算符