【译】SQL 函数 PATINDEX 及其用例入门

原文链接：https://dzone.com/articles/getting-started-with-sql-function-patindex-and-its
作者：Manvendra Deo Singh

今天，我将解释一个 SQL Server 字符串函数 PATINDEX() 用于返回一个整数值，该整数值表示模式在指定表达式中第一次出现的起始位置。如果在指定的表达式中找不到模式，此函数将返回零。

该函数的语法如下所示：

-- Syntax

PATINDEX (‘%pattern%’, expression )
复制

这里，

Pattern 是一个字符表达式，将为其返回起始位置。我们还可以使用 %、_、[]、[^] 等通配符作为模式参数。

表达式是输入字符串或列名，将在其中搜索指定模式以获取其起始位置。

如果将上述两个参数中的任何一个指定为 NULL，T-SQL 函数 PATINDEX 也将在输出中返回 NULL。尽管 PATINDEX 使用输入表达式的排序规则进行比较，但如果要在指定的排序规则中执行比较，也可以通过指定 COLLATE 来应用输入的显式排序规则。

SQL 函数 PATINDEX 的用例

接下来，我将在下一节中演示此功能的各种用例。您可以使用 SQL Server 管理工作室或任何第三方工具（如 dbForge SQL 工具）来练习这些用例。此 SQL 工具提供了许多可帮助 Microsoft SQL Server 开发人员的基本功能。

用例 1：使用带有输入字符串的 PATINDEX
让我们从它的第一个用例开始，我们将指定一个输入字符串，然后从指定的输入字符串中搜索一个模式。我已将输入字符串指定为“我的名字是 Manvendra Deo Singh”，并将模式指定为“Deo”，以获取其在此字符串中的起始位置。起始位置将从第一个字符开始计算为数字 1，并将继续按递增顺序进行。

我已经展示了如何为每个字符分配一个数字来表示其位置，如上表所示。这里的数字被分配给每个字符，包括空格。

让我们在 T-SQL 语句下面运行以返回字符串模式“Deo”的起始位置。

-- use PATINDEX with the input string

SELECT PATINDEX (‘%Deo%’, ‘My name is Manvendra Deo Singh’ ) As [pattern position]
复制

你可以在下图中看到它的输出，显示为 22。现在，你可以看到这个字符串的起始位置，“Deo”。我在上表中突出显示了它的起始位置，显示为 22。

我们可以通过更改模式字符串来使用相同的 T-SQL 语句来举另一个例子。现在，我已经搜索了模式字符串“My”、“M”和“Manvendra”的起始位置。

-- use PATINDEX with the input string

SELECT PATINDEX (‘%My%’, ‘My name is Manvendra Deo Singh’ ) AS [‘My’ position],

    PATINDEX (‘%M%’, ‘My name is Manvendra Deo Singh’ ) AS [‘M’ position],

    PATINDEX (‘%Manvendra%’, ‘My name is Manvendra Deo Singh’ ) AS [‘Manvendra’ position]
复制

您将在此查询的输出下方看到前 2 次搜索显示与 1 相同的结果，因为 M 是此字符串的第一个字符。

接下来，如果我们使用 NULL 作为模式字符串，我将显示此查询的结果。正如我上面所说，如果我们为此参数指定 NULL，我们的输出也将返回为 NULL。因此，运行以下语句以获取输出。

-- use PATINDEX with NULL

SELECT PATINDEX (NULL, ‘My name is Manvendra Deo Singh’ ) 
复制

我们可以看到上述查询的输出也显示为 NULL。

让我们再举一个例子，我们将搜索一个不属于指定字符串的模式字符串，并查看它的输出应该是 0.p

-- use PATINDEX with the input string

SELECT PATINDEX (‘%SQL%’, ‘My name is Manvendra Deo Singh’ ) AS [Pattern position]
复制

我搜索了“SQL”模式字符串以从指定的输入字符串中获取其起始位置。不幸的是，上述查询的输出显示为 0，因为指定输入中没有“SQL”字符串。

用例 2：将 PATINDEX 与列一起使用
我已经根据在第一个用例中指定输入字符串解释了此函数的各种示例。现在，我将向您展示基于指定表格列的其他示例。正如我上面提到的，我们还可以在列上使用此函数来显示存储在该列上的所有行的所需结果集。

举个例子，我在 sys.databases 表中使用了 PATINDEX SQL 函数来查找模式在 collat​​ion_name 列中的位置。

-- use PATINDEX with the input string

SELECT name, collation_name, PATINDEX (‘%CI_AS%’, collation_name) AS [Pattern position]

FROM sys.databases

WHERE database_id>4
复制

我已经执行了上面的 SQL 语句并得到了下面的输出。我正在 collat​​ion_name 列中搜索模式“CI_AS”位置。我们可以看到结果集中的差异。一个数据库在 CI_AS 不存在的排序规则类型上运行，而其他数据库在不同位置具有此字符串，这些位置在以下输出的“模式位置”列中给出。

您可以更改表达式并为存储在列中的数据获得所需的结果。

用例 3：使用带有通配符的 PATINDEX
本节将帮助您了解带有通配符的 PATINDEX 的各种用例。如上所述，我们可以使用通配符作为模式来返回所需的结果集。查看通配符，它​​可用于返回输出以供各种用途。

我们使用 % 以防我们需要匹配任意长度的字符串

如果我们需要匹配单个字符，我们可以使用 _

如果您需要匹配括号中提到的任何字符，我们可以使用 []

最后，[^] 用于匹配任何不在 [^] 中的字符

在 PADINDEX 函数中使用这些通配符来获取基于上述解释的输出。我已经在上面的示例中显示了 %，所以让我们关注其他通配符。

-- use PATINDEX with the input string

SELECT PATINDEX (‘%M_%’, ‘My name is Manvendra Deo Singh’ ) AS [‘M_’ position],

    PATINDEX (‘%M__vendra%’, ‘My name is Manvendra Deo Singh’ ) AS [‘M__vendra’ position]
复制

在上面的示例中，我使用了 （下划线）和 % 通配符。第一条语句返回第一个字符串的起始位置，它以 M 字母开头。相反，第二条语句 (M__vendra) 将返回第一个字符串的起始位置，第一个字母为 M，在 2 个后续字母之后（ 考虑任何字母），其余所有字母都是 vendra。

查看输出；第一个语句返回字符串 My 的起始位置，而第二个语句返回字符串 Manvendra 的起始位置。

在下面的示例中，让我们在系统表的列名上测试类似的场景。

-- use PATINDEX with wildcard

SELECT name, collation_name, PATINDEX (‘%C_%’, collation_name) AS [Pattern position]

FROM sys.databases

WHERE database_id>4
复制

在这里，我们正在搜索字符串的起始位置，它以字母 C 开头，在排序规则名称列中带有下划线通配符。如果您查看以下输出的前 2 行，则起始位置返回相同，而两者都用于不同的字符串。

用例 4：在 WHERE 子句中使用 PATINDEX
我们也可以在 WHERE 子句中使用这个字符串函数，根据这个函数定义的匹配条件返回特定的结果。同样，我使用系统表 sys.databases 表来演示这个用例。

-- use PATINDEX in the WHERE clause

SELECT name, collation_name

FROM sys.databases

WHERE PATINDEX (‘%[0-9]%’, collation_name)>10
复制

假设我们想要获取其排序规则中有一个数字字母位于第 10 个以上字符的位置的数据库列表。第二条语句显示了类似的结果，其中排序规则的数字字母放置在第 9 个字符以上的位置。

看看这两个语句的输出。

下面是另一个示例，我正在搜索名称中第 4 个字符后包含字符“db”的所有数据库的列表。第二条语句显示了名称中第 4 个字符后带有数字值的数据库列表。

-- use PATINDEX in the WHERE clause

SELECT name, create_date

FROM sys.databases

WHERE PATINDEX (‘%db%’, name)>4

GO

SELECT name, create_date

FROM sys.databases

WHERE PATINDEX (‘%[0-9]%’, name)>4
复制

这是输出，在这里我们可以看到并验证它们。例如，第一个结果显示名称在第 4 个字符位置之后具有 DB 字符的所有数据库。相比之下，第二个输出显示我们只有一个数据库，它们的名称在第 4 个字符之后是数字值。

结论
我们在本文中学习 SQL 函数 PATINDEX 及其用例。尝试这些用例来练习在您的需求中使用此 SQL 函数。