How to use multiple CHARINDEX() function in a single query in SQL Server

Syntax CHARINDEX()

Let's see the syntax first.

CHARINDEX (expression_to_find, expression_to_search, start_location)

The function takes three arguments.

1) expression_to_find: The character or text that you want find in a given string.
2) expression_to_search: The string in which you want to find a particular character or text.
3) start_location: The position (or index, or location) from where it will begin the search.

Related tutorial: How to use the MIN() function inside the "where" clause?

Here's a simple CHARINDEX() function example.

SELECT CHARINDEX('A', 'alpha bravo charlie')

The result will be "1". Why? Because, by default it will return the 1st location of A in the string.

Using CHARINDEX() with table... How to get data from table based on a particular character in SQL Server

Now, here's another scenario.

I want to find the location of "a" in "bravo", which is the 3rd location. The string remains the same "alpha bravo charlie". Assuming, I do not know the location.

I can now use multiple "CHARINDEX()" to find the location of "a" in "bravo".

SELECT CHARINDEX('A', 'alpha bravo charlie',
	CHARINDEX('b', 'alpha bravo charlie') + 1) CharLocation

The second "CHARINDEX" gives me the location of "b" in bravo, which helps me find the 3rd location of "a" in the string.

The result is: 9

🙂

← PreviousNext →