June 29, 2021

The SQL CHARINDEX function returns the starting position of a character expression in another character expression.

Description

The CHARINDEX function helps with string data manipulation like some other SQL functions of SUBSTRING, REPLACE, CONCAT, etc. The CHARINDEX function finds the beginning of a character expression in a given character expression. The result of the CHARINDEX function is the index position.

The CHARINDEX function is used to find the position of one string within another.  This can be handy when you need to break data apart by dashes or commas.

The general form for the CHARINDEX function is

CHARRINDEX(value to find, value to search)

Where value to find is one or more characters that you wish to find in the value to search.

If the value is found, then the starting positon is returned.  If the value isn’t found then 0 (zero) is returned.

If either the value to find, or value to search are NULL, then NULL is returned.

Visual example of CHARINDEX
CHARINDEX in action!

SQL CHARINDEX Usage Notes

The first argument of the CHARINDEX function is the character expression or the substring whose position is to be found. The limit is 8000 characters.

The second argument of the CHARINDEX function is the character expression that is searched for the starting position of the first character expression.

The third argument is the starting position. If it is not specified or is a negative number, then the search begins from position 1. This argument helps when we do not want to start the search from the beginning. We can provide any starting position. The CHARINDEX function starts indexing from 1 instead of 0.

The CHRAINDEX function returns an integer value. The return value is bigint in case of nvarchar(max), varchar(max), or varbinary(max). The input character expression cannot be an image or a text data type. If the character expression is not present in the main expression, then a zero value returns. If any of the first two arguments are NULL, then a NULL value returns.

CHARINDEX can perform both case-sensitive and case-insensitive matching for the expression.

Syntax

CHARINDEX (sub_expression, main_expression, start_position (optional))

SQL CHARINDEX Examples

Following is a simple example of using a CHARINDEX function in SQL.

SELECT CHARINDEX('charindex', 'The CHARINDEX function', 4) position;
/* Answer */
SELECT  CHARINDEX('charindex', 
'The CHARINDEX function', 4) position;

The CHARINDEX function searches the position of the ‘charindex’ expression and finds it although it is in the lower case. The starting position of 4 begins searching from position 4. The indexing remains the same, and we get position 5 as the starting index of the sub expression. The result is the same if we do not specify the start position.

Below, we will see how to perform a case-sensitive search using the CHARINDEX function.

SELECT CHARINDEX('charindex', 'The CHARINDEX function' COLLATE Latin1_General_CS_AS) position;
/* Answer */
SELECT  CHARINDEX('charindex', 
'The CHARINDEX function' COLLATE Latin1_General_CS_AS) position;

The output is a zero value because ‘charindex’ is not present in the string expression in the lower case.

The following example uses the AdevntureWorks2019 database. The CHARINDEX function finds the starting position of skills information in the resume of a job candidate.

SELECT TOP(3) CHARINDEX('Skills', CONVERT(varchar(8000), Resume)) As skills_position FROM HumanResources.JobCandidate;
/* Answer */
SELECT TOP(3) CHARINDEX('Skills',
CONVERT(varchar(8000), Resume)) As skills_position
FROM HumanResources.JobCandidate;

The TOP(3) function selects the top three records in the Job Candidate table. Another point to note is the use of the convert function. As the data type of the Resume column is XML, the Convert function converts the data type from XML to varchar. After conversion, the CHARINDEX function searches for the expression ‘Skills’ in the resume data and returns its starting location. In this way, the CHARINDEX function helps to go through lengthy records and locate the specific data.

See Also

About the author 

Kris Wenzel

Kris Wenzel has been working with databases over the past 30 years as a developer, analyst, and DBA. He has a BSE in Computer Engineering from the University of Michigan and a MBA from the University of Notre Dame. Kris has written hundreds of blog articles and many online courses. He loves helping others learn SQL.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Are YOU READY TO START LEARNING SQL SERVER, BUT DON'T KNOW WHERE TO START?

Nothing is worse than, being excited to learn a new tool but not knowing where to start, wasting time learning the wrong features, and being overwhelmed .

But it doesn't have to be this way.

I'm Putting together a free email course to help you get started learning SQL Server.

Name*
Email*
>