charindex函数_什么是SQL Server CHARINDEX（）函数？

charindex函数

Hey, readers! Hope you all are doing well. In this article, we will be focusing on SQL Server CHARINDEX() function in detail.

嘿，读者们！ 希望大家一切都好。 在本文中，我们将重点关注SQL Server CHARINDEX（）函数 。

---

---

SQL Server CHARINDEX（）函数的工作 (Working of SQL Server CHARINDEX() function)

SQL Server has various functions to work and manipulate with the string data. One such function is CHARINDEX().

SQL Server具有用于处理和处理字符串数据的各种功能。 CHARINDEX（）是此类函数之一。

SQL CHARINDEX() function enables us to search for a particular substring within a string or a set of strings. Further, if the substring is found, the CHARINDEX() function returns the position of the substring in the string.

SQL CHARINDEX() function使我们能够搜索一个字符串或一组字符串中的特定子字符串。 此外，如果找到了子字符串，则CHARINDEX（）函数返回子字符串在字符串中的位置。

Let us understand this concept with the help of an example.

让我们借助示例来理解这个概念。

Consider an online portal for a recruitment firm. They enroll the students/customers for placement assistance by collecting the details of everyone. If the owner of the firm wants to check whether the email id of each one is according to the standard format or not, the developers can use SQL CHARINDEX() function for the same.

考虑一个招聘公司的在线门户。 他们通过收集每个人的详细信息来招募学生/客户以获取安置帮助。 如果公司的所有者想检查每个电子邮件的电子邮件ID是否符合标准格式，则开发人员可以使用SQL CHARINDEX（）函数来实现。

By using this function, they can check whether the character ‘@’ is present within the entered email id or not.

通过使用此功能，他们可以检查输入的电子邮件ID中是​​否存在字符“ @”。

I hope you guys have understood the working and importance of CHARINDEX() function.

我希望你们已经了解了CHARINDEX（）函数的工作原理和重要性。

Let us now go through the syntax of the same in the upcoming section.

现在，让我们在下一部分中介绍相同的语法。

---

---

CHARINDEX（）函数的结构 (Structure of CHARINDEX() function)

The CHARINDEX() function searches for the presence of a particular substring within a string. If found, it returns the index of the substring.

CHARINDEX() function搜索字符串中是否存在特定的子字符串。 如果找到，它将返回子字符串的索引。

CHARINDEX(sub-string, data, start_index)

• sub-string: String to search.
  sub-string ：要搜索sub-string 。
• data: The set of input strings.
  data ：输入字符串的集合。
• start_index(Optional): The position from which the search will begin.
  start_index （可选）：搜索开始的位置。

Moreover, if the substring is not found, the CHARINDEX() function returns zero(0).

此外，如果未找到子字符串，则CHARINDEX（）函数将返回零（0）。

Now, let us implement the CHARINDEX() function through the below examples.

现在，让我们通过以下示例实现CHARINDEX（）函数。

---

---

通过示例实现SQL Server CHARINDEX（）函数 (Implementing SQL Server CHARINDEX() function through examples)

In the below example, we have searched for the presence of ‘.’ in the provided substring. Further, the CHARINDEX() function has returned the position of the same.

在下面的示例中，我们搜索了“。”的存在。 在提供的子字符串中。 此外，CHARINDEX（）函数已返回其位置。

SELECT CHARINDEX('.', 'python@journaldev.com') AS Position;

Output:

输出：

18

Now, we have looked for the presence of ‘Pune’ in the command and provided the starting index as 5. Also, we have looked for ‘Name’ in the below provided string.

现在，我们在命令中查找了“ Pune”的存在，并将起始索引提供为5。此外，我们还在下面提供的字符串中寻找了“ Name”。

SELECT 
    CHARINDEX('Pune','I live in Pune',5) AS Present_City,
    CHARINDEX('Name','Hello Everyone') AS Name;

As understood, the substring ‘Name’ is not present in the string. Therefore, the CHARINDEX() function has returned 0.

可以理解，字符串中不存在子字符串“名称”。 因此，CHARINDEX（）函数已返回0。

Output:

输出：

SQL Server CHARINDEX() Function With start_index 带start_indexSQL Server CHARINDEX（）函数

We have now created a table ‘Info’ with the below data columns:

现在，我们使用以下数据列创建了一个“信息”表：

• id
  ID
• email
  电子邮件

Further, we have inserted the data into the created table within the SQL Server database.

此外，我们已将数据插入到SQL Server数据库中创建的表中。

CREATE TABLE Info (
    id INT PRIMARY KEY,
    email VARCHAR (255) NOT NULL
); 

INSERT INTO Info (
   id,
   email
)
VALUES
    (
        1,
      'jim@gmail.com  '
    ),
    (
        2,
      'xyz123@vis.ac.in  '
    ),
    (
        3,
      ' georgegmail.com '
    );

Output:

输出：

SQL Server CHARINDEX() Function Create Table SQL Server CHARINDEX（）函数创建表

Now, we have applied CHARINDEX() function to search for the presence of ‘@’ within each data value of the column ’email’.

现在，我们已应用CHARINDEX（）函数来搜索“电子邮件”列的每个数据值中是否存在“ @”。

SELECT email, 
              CHARINDEX('@', email) AS "Presence of @"
FROM Info;

Output:

输出：

SQL Server CHARINDEX() Function Example SQL Server CHARINDEX（）函数示例

---

---

结论 (Conclusion)

By this, we have come to the end of this topic. Feel free to comment below, in case you come across any doubt.

至此，我们到了本主题的结尾。 如果您有任何疑问，请在下面发表评论。

For more such posts related to SQL Server, visit SQL Server JournalDev.

有关与SQL Server相关的更多此类帖子，请访问SQL Server JournalDev 。

---

---

参考资料 (References)

• SQL Server CHARINDEX() — Documentation
  SQL Server CHARINDEX（）—文档

---

---

翻译自: https://www.journaldev.com/42270/sql-server-charindex-function

charindex函数