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.

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

See the section below for seeing the examples and results as using the CHARINDEX SQL Server function.

The example of using CHARINDEX function

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

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

See the SELECT statement with CHARINDEX and the result:

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:

The output:

SQL CHARINDEX starting

You may also notice that the SQL Server’s CHARINDEX function is not case sensitive. The position of first ‘t’ is though 8 but CHARINDEX returned 20 as we specified to search the letter ‘t’ from position 9.

What if given substring is not found?

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

The query:

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

For showing the usage of 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 position of ‘M’ for each record.

The CHARINDEX query:

The result-set:

SQL CHARINDEX table

The second column in above graphic shows the position of letter ‘M’ (if exists) for each employee. If 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 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:

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 as using the CHARINDEX function. If you simply used 5, the following error would have raised:

Msg 8116, Level 16, State 1, Line 1

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