本文共 4494 字,大约阅读时间需要 14 分钟。
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 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.
现在,让我们在下一部分中介绍相同的语法。
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()函数。
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:
输出:
We have now created a table ‘Info’ with the below data columns:
现在,我们使用以下数据列创建了一个“信息”表:
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:
输出:
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:
输出:
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相关的更多此类帖子,请访问 。
翻译自:
charindex函数
转载地址:http://mnlzd.baihongyu.com/