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.
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.
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.
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.
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.
