SQL Server CHARINDEX() function with example

Last updated: 20th June 2023

Syntax

CHARINDEX (expression_to_find, expression_to_search, start_location)

The CHARINDEX() function takes "three" parameters. The third or the final parameter, which is an Integer value, is "optional". Let us understand all the parameters in detail.

1) expression_to_find

The first parameter can be a character or a string of characters that we want to search in another string.

Let us assume we have a string Light Years Away and we want to get the location of the word "Years" in the string. So the first parameter will be the word Years in the function.

SELECT CHARINDEX('YEARS', 'Light Years Away') Location

The result is 7, which means that the word "Years" starts at location "seven" in the above string. A sentence like this can have multiple occurance of a particular word or character, but the function will find the first location of the expression. The later occurances will be ignored.

2) expression_to_search

An expression can be a phrase or a sentence with characters like alphabet, numbers and other special characters. The second parameter in "CHARINDEX()" is typically a string with many characters. This parameter can also be a variable or the name of a column in a table.

3) start_location

The hird parameter is optional (you may or may not use it) and it is an "Integer" value. In case you wish to locate or find an expression (expression_to_find) starting from a particular location in a string (expression_to_search), then you have to put the figure. If you do not assign any value for the third parameter in the function, then the search starts from the position "0".

Related What is SUBSTRING() function in SQL Server and why use it?

More examples

Here in our example, we have a list of categories separated by the symbol pipe |.

PHP | JAVA | PYTHON

1) The query to find the first position of the symbol “|” will be as follows.

SELECT CHARINDEX('|', 'PHP | JAVA | PYTHON') Location

The result is 5.

2) Using start_location

There are three categories separated by two pipes and we now need to find the position of the "second pipe" in the string. To do this we have to mention a start_location after the first pipe, since the default search will start from "0" and the result will end up showing location "5".

SELECT CHARINDEX('|', 'PHP | JAVA | PYTHON', 6) Location

The result is now 12.

3) Using multiple CHARINDEX function in a single query.

Continuing with example 2, let us assume we do not have the start_location before hand an we have to find it dynamically.

SELECT CHARINDEX('|', 'PHP | JAVA | PYTHON', CHARINDEX('|', 'PHP | JAVA | PYTHON') + 1) Location

Or

WITH TAG AS (SELECT 'PHP | JAVA | PYTHON' AS Category)
SELECT CHARINDEX('|', Category, CHARINDEX('|', 'PHP | JAVA | PYTHON') + 1) Location
FROM TAG

The result is 12.

4) Ok, we found locations of the pipe. Now, using the locations we need to extract a value from the above-mentioned string.

WITH TAG AS (SELECT 'PHP | JAVA | PYTHON' AS Category)
SELECT SUBSTRING(Category, 1, CHARINDEX('|', Category) - 1) Category
FROM TAG

The result is PHP.

In the above example, we are using another SQL function called the SUBSTRING in accord with CHARINDEX to get a value from the searched string.

Here's another CHARINDEX function example, I am sure you will like: 👉 How to get data from table based on a particular value in SQL Server

← PreviousNext →