Hey, readers! Hope you all are doing well. In this article, we will be focusing on SQL Server CHARINDEX() function in detail.
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 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.
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.
By using this function, they can check whether the character ‘@’ is present within the entered email id or not.
I hope you guys have understood the working and importance of CHARINDEX() function.
Let us now go through the syntax of the same in the upcoming section.
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.
|
1 |
CHARINDEX(sub-string, data, start_index) |
sub-string: String to search.data: The set of input strings.start_index(Optional): The position from which the search will begin.
Moreover, if the substring is not found, the CHARINDEX() function returns zero(0).
Now, let us implement the CHARINDEX() function through the below examples.
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.
|
1 |
Output:
|
1 2 |
<span style="color: #008000;"><strong>18 </strong></span> |
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.
|
1 2 3 |
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.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
CREATE TABLE Info ( id INT PRIMARY KEY, email VARCHAR (255) NOT NULL ); INSERT INTO Info ( id, email ) VALUES ( 1, ), ( 2, ), ( 3, ' georgegmail.com ' ); |
Output:
Now, we have applied CHARINDEX() function to search for the presence of ‘@’ within each data value of the column ’email’.
|
1 2 3 |
SELECT email, CHARINDEX('@', email) AS "Presence of @" FROM Info; |
Output:
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.