The CHARINDEX function in SQL

The SQL CHARINDEX function is used to return the position of the specified substring in a string.

For example, finding the position of ‘@’ in an email address:

SELECT CHARINDEX(‘@’, ’email@test.com’);

On that basis, let me explain the general syntax of using the CHARINDEX function.

Arguments Description
expressionToFind The ‘@’ specifies the first argument where the expression to be found is provided.
expressionToSearch The ‘email@test.com’ is the second argument that specifies the string where you want to search the first expression.
start_location
  • The CHARINDEX has a third optional argument as well.
  • This is the start location in the string which is an int type.
  • The counting starts from 1.
  • If no match is found in the given string, the CHARINDEX returns 0.

See the section below for the examples using the CHARINDEX SQL Server function.

The example of using CHARINDEX function

I will show you using the table column in the coming example, let me start with a simple example to explain how the CHARINDEX function works.

I am using the same example as in the introductory section i.e. finding the position of the ‘@’ sign in a given email address.

See the SELECT statement with CHARINDEX and the result:

SELECT CHARINDEX('@', 'abc@email.com') AS "The @ position";

The result:

SQL CHARINDEX

You can see the @ sign is found at position four and the counting started from 1.

Specifying the third argument – starting position

In this example, the starting position is also provided in the CHARINDEX function. We will search the following string:

“Hello, this is SQL Tutorial”

In this string, we will find the position of letter‘t’ by giving the starting position 9:

SELECT CHARINDEX('t', 'Hello, this is SQL Tutorial', 9)

AS "The position";

The output:

SQL CHARINDEX starting

SQL Server’s CHARINDEX function is not case-sensitive. In above example, the position of the first ‘t’ is 8 but CHARINDEX returned 20 as we specified to search the letter ‘t’ from position 9.

What if the given substring is not found?

Now see the example where the given substring/expression is not found in the CHARINDEX function.

The query:

SELECT CHARINDEX('there', 'Search Something Here')

AS "The position";

The result:

SQL CHARINDEX zero

As such, there is no occurrence of the word “there” in the searched string so CHARINDEX returned 0.

Using the CHARINDEX function with table column example

To show the usage of the CHARINDEX function, I am using the employee table.

  • We have a column emp_name and CHARINDEX is used to find the location of the letter ‘M’ for the entire column.
  • The query retrieves the employee names, salary, and age along with the position of ‘M’ for each record.

The CHARINDEX query:

SELECT emp_name,

CHARINDEX('M', emp_name) AS "Position of M",

emp_salary,

emp_age 

FROM sto_employees;

The result-set:

SQL CHARINDEX table

The second column in the above graphic shows the position of the letter ‘M’ (if exists) for each employee. If the employee name did not include this, the respective value displayed is 0.

For the 7th record, the emp_name value is NULL, so the CHARINDEX also returned NULL for this record.

Can we search for a numeric field?

The example below shows using the numeric field. For that, we will find the position of 0 in the emp_salary column. See what we get this time:

SELECT emp_name,

emp_salary,

CHARINDEX('5', emp_salary) AS "Numeric position of salary",

emp_age 

FROM sto_employees;

The result-set:

SQL CHARINDEX numeric

Yes, we can search a numeric column by using the CHARINDEX function. However, you might notice the value to be searched is still enclosed in the single quotes i.e. ‘5’.

Remember, the first argument must be a string while using the CHARINDEX function. If you simply used 5, the following error would have been raised:

Msg 8116, Level 16, State 1, Line 1

Argument data type int is invalid for argument 1 of charindex function.

Author - Atiq Zia

Atiq is the writer at jquery-az.com, an online tutorial website started in 2014. With a passion for coding and solutions, I navigate through various languages and frameworks. Follow along as we unravel the mysteries of coding together!