Charindex SQL

Introduction to Charindex SQL

Charindex in SQL is used to search for the position of a certain string or character in some other string. We can use this functionality to find the presence and location of the substring or certain character inside the text by simply specifying the string in which we want to search and the string that we wish to search inside that string. Further, we can optionally specify the starting location from where the search in the original string needs to begin. We have to be careful and know about the working of the CHARINDEX function. Because in case if there are multiple occurrences of the character, expression of string that we are searching for in the main string, then the function will return the position of only the first occurrence of the searched expression.

Syntax

The syntax of the CHARINDEX function is as shown below –

CHARINDEX ( Search Expression, Original Expression [, Beginning Location ] )

Search Expression and Original Expression parameters are required parameters and need to be mentioned compulsorily, while the third parameter, Beginning Location, is optional. Search expression is the character of the string that we want to search for and whose position we wish to find out in the original string. The original string is the text, string, or character array in which we wish to search for a particular string.

Beginning location is the index from which you specify that the search for the search expression in original expression should begin with. It is optional, and hence when not specified, it has the default value of 0 as the index from where the function will begin searching. Hence, when none of the values is specified in the beginning location parameter, the search for search expression inside the original expression will begin from the start of the original string. Note that the beginning location parameter, if specified, should always be an integer value that stands for index.

Working of Charindex SQL function

CHARINDEX function internally searches for the characters that are specified in the search expression inside the original expression from the first position that is index 0 in the original string. This is done when the beginning location parameter is not specified. If an integer value is mentioned as the beginning location, then the index in the original string from where the search for the search expression will begin will be the value specified in the third parameter.

When the search for the string is being made, then as soon as the function comes across the search expression value inside the original expression, then it stops the search right there, and the index where the string is found is returned from the function. Hence, in the case of multiple occurrences of the search expression in the original expression, only the first occurrence of the searched expression is retrieved if the beginning location is not specified; else, the first occurrence of the search expression after the beginning location index is retrieved.

Examples of Charindex SQL

Let us consider one example where we will try to find the @ symbol inside some of the email addresses, say payaludhani26@gmail.com. We have to locate the index of the @ symbol in the given mail address. For this, we will use the CHARINDEX function with search expression value as @, original string parameter with value payaludhani26@gmail.com, and none of the value as the beginning location as we want to begin the search from index 0 in the original string. Our query statement will be as follows –

SELECT CHARINDEX ('@','payaludhani26@gmail.com') as 'AddressSignPosition';

that gives the following output after the execution of the query statement –

Let us consider some other example; we have a string “Hey! Good Morning My Friend! How are you doing?”. We have to search for the second position of the exclamatory sign in the above string. For that, we would have to skip the index of the first occurrence in the search string, which is 4 in our example. Hence, we will start the search for the exclamatory sign from index 5 by specifying the beginning value parameter as 5 and the search expression as ‘!’ Sign with an original string having the value “Hey! Good Morning My Friend! How are you doing?”. Our query statement will be as follows –

SELECT CHARINDEX ('!','Hey! Good Morning My Friend! How are you doing?',5) as 'exclamatoryPosition';

that gives the following output after execution –

Instead of specifying the beginning position, if we would have directly searched for the exclamatory sign in the above example, then our query statement would have been as follows –

SELECT CHARINDEX ('!','Hey! Good Morning My Friend! How are you doing?') as 'exclamatoryPosition';

and the output of the execution of the above query is as follows –

Instead of the character, now let us search ceratin substring in the main string. Consider the following example, where we will store the string to be searched in a variable and then specify it in the CHARINDEX function as shown below –

DECLARE @SearchString varchar(100)
SET @SearchString = 'Hey! Good Morning My Friend! Learn SQL with EDUCBA with Payal.'
SELECT CHARINDEX ('SQL', @SearchString) AS 'topicPosition'

The execution of the above statements gives the following output with the resultant position as 36 that is the position of SQL in the provided string.

Let us see what happens if the string or character being searched is not present in the original string. Consider the following example –

DECLARE @SearchString varchar(100)
SET @SearchString = 'Hey! Good Morning My Friend! Learn Data Science on EDUCBA with Payal.'
SELECT CHARINDEX ('SQL', @SearchString) AS 'topicPosition'

that gives the following output with 0 position, which means unavailability of the search string.

Conclusion

We can find out the presence and location of the particular character or substring inside the original string using the function CHARINDEX() in SQL. Further, we can customize the search by specifying the index from where the search for our expression should begin in the original string by specifying the beginning location, which is again an integer value that stands for index.

Recommended Articles

This is a guide to Charindex SQL. Here we discuss the Working of Charindex SQL and the Examples along with the statements and outputs. You may also have a look at the following articles to learn more –

1. MySQL Split
2. SQL Temporary Table
3. SQL Table Partitioning
4. SQL NOT Operator