sql运算符_SQL LIKE运算符概述

sql运算符

In this article, we are going to learn how to use the SQL LIKE operator, in SQL Server, using regular expressions to find and/or manipulate text. We will start by learning the symbols and basic syntax of using wildcard regular expressions. We will use character sets and repetition expressions to create flexible matching patterns, and along the way, we’ll examine different ways to use the LIKE operator. And then, finally, in the latter part of the section, we will explore some of the most common and most useful regular expression examples.

在本文中，我们将学习如何在SQL Server中使用SQL LIKE运算符，并使用正则表达式查找和/或操作文本。 我们将从学习使用通配符正则表达式的符号和基本语法开始。 我们将使用字符集和重复表达式来创建灵活的匹配模式，并且在此过程中，我们将研究使用LIKE运算符的不同方法。 然后，最后，在本节的后半部分，我们将探讨一些最常见和最有用的正则表达式示例。

SQL is the most commonly used language to work with databases. When you design a report or use BI or any reporting tool, the software is almost certainly building an SQL query behind the scenes which runs on the database and returns your selected data. When we’re looking for specific data or the data that fits specific criteria, the where clause provides the toolset you need. This gives an option to query specific rows that we’re looking for instead of the entire table.

SQL是用于数据库的最常用语言。 当您设计报告或使用BI或任何报告工具时，几乎可以肯定该软件在幕后建立了一个SQL查询，该查询在数据库上运行并返回您选择的数据。 当我们寻找特定数据或适合特定条件的数据时，where子句提供了您需要的工具集。 这提供了查询我们要查找的特定行而不是整个表的选项。

Pre-requisites

先决条件

Download the AdventureWorks2014 database here to test the following T-SQL samples.

在此处下载AdventureWorks2014数据库，以测试以下T-SQL示例。

Getting Started

入门

Let us walk-through the SQL statements using the LIKE keyword and wildcard characters. So, let’s get started learning about SQL LIKE operator.

让我们使用LIKE关键字和通配符来遍历SQL语句。 因此，让我们开始学习有关SQL LIKE运算符的知识。

Using SQL LIKE Wildcard Character examples

使用SQL LIKE通配符示例

Regular expressions are patterns for describing how to match strings in a WHERE clause. Many programming languages support regular expressions that use slightly different syntax from what is used with the LIKE operator. In this article, when we refer to regular expressions, we’re referring to the patterns used with the SQL LIKE operator

正则表达式是用于描述如何在WHERE子句中匹配字符串的模式。 许多编程语言都支持正则表达式，这些正则表达式使用的语法与LIKE运算符使用的语法略有不同。 在本文中，当我们引用正则表达式时，是指与SQL LIKE运算符一起使用的模式

The following table includes the four different wildcard characters. You can also refer the article SQL string functions for Data Munging (Wrangling) for more examples.

下表包括四个不同的通配符。 您也可以参考文章SQL字符串函数进行数据整理（争用）以获取更多示例。

Wildcard characters	Description
%	Any string with zero or more characters in the search pattern
_	Any single character search with the specified pattern
[]	Any single character search within the specified range
[^]	Any single character search not within the specified range

通配符	描述
％	搜索模式中具有零个或多个字符的任何字符串
_	具有指定模式的任何单个字符搜索
[]	在指定范围内的任何单个字符搜索
[^]	不在指定范围内的任何单个字符搜索

Using SQL LIKE with ‘%’ wildcard character

将SQL LIKE与'％'通配符一起使用

The following SQL statement returns all of the rows of person table where their last name starts with the letter A. Let us specify the letter ‘A’, the first character that needs to be in the string and then use the wildcard ‘%’, the percent.

以下SQL语句返回人员表的所有行，其姓氏以字母A开头。让我们指定字母“ A”，即需要在字符串中使用的第一个字符，然后使用通配符“％”，百分比。

SELECT TOP 10 *
FROM Person.Person
WHERE firstname LIKE 'A%';

You’ll see the output that lists top 10 rows of the person table where the firstname starts with A and the rest of the character is unknown.

您将看到输出，列出了人员表的前10行，其中名字以A开头，其余字符未知。

Using SQL LIKE with the ‘_’ wildcard character

将SQL LIKE与'_'通配符一起使用

The wildcard, underscore, is for matching any single character. The following SQL statement finds all telephone numbers that have an area code starting with 7 and ending in 8 in the phonenumber column. We’ve also included % wildcard character at the end of the search pattern as we’re not concerned with the rest of the string values.

下划线通配符用于匹配任何单个字符。 以下SQL语句在phonenumber列中查找所有区号以7开头并以8结尾的电话号码 。 由于我们不关心其余的字符串值，因此我们还在搜索模式的末尾添加了通配符％。

SELECT p.FirstName, 
       p.LastName, 
       PhoneNumber
FROM Person.PersonPhone AS ph
     INNER JOIN Person.Person AS p ON ph.BusinessEntityID = p.BusinessEntityID
WHERE ph.PhoneNumber LIKE '7_8%'
ORDER BY p.LastName;

The output shows that the area code of that start with 7 and ends with 8 are listed.

输出显示列出了以7开头和以8结尾的区号。

将SQL LIKE与[]通配符一起使用 (Using SQL LIKE with the [ ] wildcard characters)

Square brackets e.g [ ] allow us to identify multiple single characters that would be in that particular position. For example, let’s say to list all the rows where first names third character start with I or K. Instead of writing multiple LIKE conditions, we can place the pattern matching set in the third position and close it in the square. The query engine first looks for ‘I’ and then looks for ‘K’.

方括号（例如[]）使我们能够识别在该特定位置的多个单个字符。 例如，假设列出所有姓氏第三个字符以I或K开头的行。代替编写多个LIKE条件，我们可以将模式匹配集放在第三个位置，然后将其封闭在正方形中。 查询引擎首先查找“ I”，然后查找“ K”。

Let’s execute the following SQL statement

让我们执行以下SQL语句

SELECT p.FirstName, 
       p.LastName, 
       PhoneNumber
FROM Person.PersonPhone AS ph
     INNER JOIN Person.Person AS p ON ph.BusinessEntityID = p.BusinessEntityID
WHERE ph.PhoneNumber LIKE '7_8%' and p.lastname like 'Ba[ik]%'
ORDER BY p.LastName;

The above query can be re-written using OR condition. It’s more like an OR condition.

可以使用OR条件重写以上查询。 它更像是OR条件。

SELECT p.FirstName, 
       p.LastName, 
       PhoneNumber
FROM Person.PersonPhone AS ph
     INNER JOIN Person.Person AS p ON ph.BusinessEntityID = p.BusinessEntityID
WHERE ph.PhoneNumber LIKE '7_8%' and (p.lastname like 'Bai%' or p.lastname like 'Bak%')
ORDER BY p.LastName;

In the output, we can see that last names where the third character is ‘I’ or ‘k’ are listed

在输出中，我们可以看到列出了第三个字符为“ I”或“ k”的姓氏

将SQL LIKE与'^'通配符一起使用 (Using SQL LIKE with the ‘^’ wildcard character)

The following SQL statement displays all the rows that do not have the letter that starts with A to D in the first character of their last name. In order to that place the tilde character in the first position of the pattern. It becomes a NOT condition.

以下SQL语句显示所有姓氏的第一个字符中没有以A到D开头的字母的行。 为了将波浪号字符放置在图案的第一个位置。 变成非条件。

SELECT p.FirstName, 
       p.LastName
FROM Person.Person p
WHERE LastName LIKE '[^a-d]%'
ORDER BY p.lastname;

Now, if I run the above query, we’ll see that all the names coming back do not have an A, B, C or D as their first character.

现在，如果运行上面的查询，我们将看到返回的所有名称都没有以A，B，C或D作为第一个字符。

对[]通配符使用SQL NOT LIKE (Using SQL NOT LIKE with the [] wildcard characters)

The following SQL statement finds all the persons where the first name column has more than 3 characters.

以下SQL语句查找名字列包含3个以上字符的所有人员。

SELECT DISTINCT 
       firstname
FROM Person.Person
WHERE firstname NOT LIKE '[a-z][a-z][a-z]';

The output list only those names where the length of the firstname is more than 3

输出只列出这些名字在名字的长度大于3

将SQL LIKE与ESCAPE子句一起使用 (Using SQL LIKE with the ESCAPE clause)

In the following SQL statement, the ESCAPE clause is used to escape the character ‘!’ to negate the meaning of ‘%’ to find the string ‘100% Free’ in the column col1 of the temp table.

在以下SQL语句中，ESCAPE子句用于转义字符'！' 否定'％'的含义，以在临时表的col1列中找到字符串'100％Free'。

DROP TABLE IF EXISTS temp;
CREATE TABLE temp(col1 VARCHAR(100));  
GO  
INSERT INTO temp
VALUES('ApexSQL Refactor is 100% Free SQL Formatter tool'), ('ApexSQL Job is 10-15% off today only');  
GO  
SELECT *
FROM TEMP;
SELECT *
FROM temp
WHERE col1 LIKE '%100!% Free%' ESCAPE '!';  
GO

The output list only those values where the search pattern ‘100% Free’ matches the col1 expression.

输出仅列出搜索模式“ 100％Free”与col1表达式匹配的那些值。

Using SQL LIKE with the CASE statement

将SQL LIKE与CASE语句一起使用

The following SQL statement pulls out all of the employees that have a phone number formatted like three-three-four digits with dashes in between (999-999-9999). The pattern is then compared with phonenumber column to derive the domestic or international categories.

以下SQL语句提取所有电话号码格式为三到三十四位且中间用破折号（ 999-999-9999 ）的员工。 然后将该模式与电话号码列进行比较，以得出国内或国际类别。

The case expression is evaluated for the specific pattern to derive the phone category type.

对案例表达式进行评估以获取特定模式，以得出电话类别类型。

SELECT p.FirstName, 
       p.LastName, 
     PhoneNumber,
       CASE WHEN ph.PhoneNumber LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' then 'Domestic Phone Number'
     ELSE 'International Phone number'
     END PhoneNumber
FROM Person.PersonPhone AS ph
     INNER JOIN Person.Person AS p ON ph.BusinessEntityID = p.BusinessEntityID
ORDER BY p.LastName;

In the output, we can see the number is classified as domestic or international. The phonenumber column is evaluated with the LIKE operator using the square bracket. The number zero to nine in the first character position is evaluated for matching pattern zero to nine, any number from zero to nine in the second character position and third and then the fourth character position must be a dash and similar logic is applied to the rest of the characters.

在输出中，我们可以看到该号码被分类为国内或国际。 “ 电话号码”列由LIKE运算符使用方括号评估。 评估第一个字符位置中的数字0到9，以匹配模式0到9，第二个字符位置和第三个字符中从零到9的任何数字，然后第四个字符位置必须是破折号，并且其余部分应用类似的逻辑的字符。

Using SQL LIKE with dynamic SQL

结合使用SQL LIKE和动态SQL

The following SQL statement returns all the employees where the lastname matches the pattern Barb. The pattern is dynamically created and compared against the expression.

以下SQL语句返回姓氏与模式Barb匹配的所有雇员。 模式是动态创建的，并与表达式进行比较。

DECLARE @ELastName VARCHAR(20)= 'Barb';
SELECT p.FirstName, 
       p.LastName, 
       a.City
FROM Person.Person p
     JOIN Person.Address a ON p.BusinessEntityID = a.AddressID
WHERE p.LastName LIKE '%'+@ELastName+'%';

The output list the matching rows for the specified pattern Barb

输出列出了指定模式的匹配行Barb

Note: By default, CHAR injects trailing blanks depending on the length of the field. Use RTRIM to suppress the trailing blanks, if you’re using the char data-type.

注意：默认情况下，CHAR会根据字段的长度注入尾随空白。 如果您使用的是char数据类型，请使用RTRIM禁止尾随空格。

In the following SQL statement, the @eLastName field is of char data type. You can see a use of RTRIM function to trim the trailing blanks.

在以下SQL语句中，@eLastName字段为char数据类型。 您会看到使用RTRIM函数来修剪尾随的空白。

DECLARE @ELastName CHAR(20)= 'Barb';
SELECT p.FirstName, 
       p.LastName, 
       a.City
FROM Person.Person p
     JOIN Person.Address a ON p.BusinessEntityID = a.AddressID
WHERE p.LastName LIKE '%'+RTRIM(@ELastName)+'%';

Using SQL Like with an IF statement

将SQL Like与IF语句一起使用

The following SQL statement, the input value is evaluated for the specific pattern in the condition clause using IF statement.

在以下SQL语句中，使用IF语句对条件子句中的特定模式评估输入值。

DECLARE @RuleName NVARCHAR(MAX)= 'SQL Sever 2019 CTP is available for preview';
IF @RuleName LIKE 'SQL Sever [0-9]% CTP is available for preview'
    PRINT 'valid input good!';
    ELSE
    PRINT 'not a valid good!';

The input string is evaluated for specific patterns using SQL like wildcard expression and returns valid input string.

使用SQL（例如通配符表达式）评估输入字符串的特定模式，并返回有效的输入字符串。

That’s all for now!

目前为止就这样了！

Summary

摘要

Thus far, we discussed various tips and four different wildcards (%,_,[], and ^] that are available with the SQL LIKE operator. It is a great searching technique for matching string of characters with the specified patterns or where we’ve not quite sure of what you’re searching aka fuzzy search. The available wildcard characters make the LIKE operator more flexible. I hope you enjoyed this article on the SQL LIKE operator in SQL Server. Feel free ask any questions in the comments below.

到目前为止，我们讨论了SQL LIKE运算符提供的各种技巧和四个不同的通配符（％，_，[]和^]。这是一种用于匹配具有指定模式或在何处匹配字符的字符串的出色搜索技术我不太确定自己要搜索的内容，也就是模糊搜索；可用的通配符使LIKE运算符更加灵活。希望您喜欢SQL Server中SQL LIKE运算符，并在下面的评论中随意提问。

翻译自: https://www.sqlshack.com/overview-of-the-sql-like-operator/

sql运算符