SQL Server CHARINDEX() Functions: Returns Starting Index of Substring

The CHARINDEX() function returns the starting position of the substring or character in another string. It returns 0 if substring is not found.

CHARINDEX(substring, string [, start_location]) 

Parameters

1. substring: The string or character value to search in another string.
2. string: The string value where you want to search substring parameter.
3. start_location: Optional. The integer value indicating the starting position where the search should begin. The index starts from 1. If nothing is specified, then it starts searching from the beginning.

Return Value

Returns an integer indicating the index position.

• The CHARINDEX() function cannot be used with image, ntext or text data types.
• If either substring or string has a NULL value, the CHARINDEX returns NULL.
• If CHARINDEX() doesn't find substring in string then it returns 0.
• The return value is the position of the substring from the beginning of the string and not from the start_location.

Example 1:

The following example searches 'e' in the string 'Hello World' and returns its index position.

SELECT CHARINDEX('e', 'Hello world') 

Example 2:

The CHARINDEX() function is not a case-sensitive search. If you search for lowercase character 'h' in the string 'Hello world', it returns 1; even though char 'H' is uppercase in the string.

SELECT CHARINDEX('h' , 'Hello World')

The following example searches for the 'nice' word in a string expression 'Have a nice day!' and returns its index position.

Select CHARINDEX('nice', 'Have a nice day!')

The above returns 8. This is the position where the word 'nice' begins in the string.

Example 3:

In the below example we specify the position from where the search should begin.

SELECT CHARINDEX('nice', 'Have a nice day!' , 5) AS Result

Example 4:

If you try to look for an expression which is not present in string, then 0 is returned.

SELECT CHARINDEX('Good', 'Have a nice day!') AS Result

Example 5:

You can perform case sensitive search using CHARINDEX() function as shown below.

If you search for the word 'Nice' with an uppercase 'N' in the string expression, then it returns 0.

SELECT CHARINDEX('Nice', 'Have a nice day!' COLLATE Latin1_General_CS_AS) AS Result

Note: Adding COLLATE Latin1_General_CS_AS makes the search case sensitive.

Default collation of the SQL Server installation SQL_Latin1_General_CP1_CI_AS is not case sensitive.

If you change the letter 'n' to lowercase in substring then CHARINDEX() returns 8 which is the starting position of the word 'nice' in the string 'Have a nice day!'

SELECT CHARINDEX('nice', 'Have a nice day!' COLLATE Latin1_General_CS_AS) AS Result