What is SQL Server CHARINDEX() function? With Examples

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.

  • 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.


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.

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



SQL Server CHARINDEX() Function With start_index

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 CHARINDEX() Function Create Table

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



SQL Server CHARINDEX() Function Example


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.


By admin

Leave a Reply

%d bloggers like this: