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:
On that basis, let me explain the general syntax of using the CHARINDEX function.
|expressionToFind||The ‘@’ specifies the first argument where the expression to be found is provided.|
|expressionToSearch||The ‘email@example.com’ is the second argument that specifies the string where you want to search the first expression.|
- 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('@', 'firstname.lastname@example.org') AS "The @ position";
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:
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";
What if the given substring is not found?
Now see the example where the given substring/expression is not found in the CHARINDEX function.
SELECT CHARINDEX('there', 'Search Something Here') AS "The position";
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 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;
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’.
Msg 8116, Level 16, State 1, Line 1
Argument data type int is invalid for argument 1 of charindex function.