What is SQL Server CHARINDEX() function?

Filed Under: SQL Server
SQL SERVER CHARINDEX() Function

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.

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.

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.

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:

SQL Server CHARINDEX() Function With start_index
SQL Server CHARINDEX() Function With start_index

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

  • id
  • email

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

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

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

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

Output:

SQL Server CHARINDEX() Function Example
SQL Server CHARINDEX() Function Example

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.


References


Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages