CHARINDEX

Returns the starting position of the first occurrence of a specified string within a string, after a specified number of characters have been searched.

Syntax


                    CHARINDEX ( 'substring' , 'string_to_search'
                    [ , start_location ]
                    )

Parameters

Parameter	Description	Data Type
`'substring'`	The character string to search for.	`VARCHAR` or `NVARCHAR`
`'string_to_search'`	The string to search within.	`VARCHAR` or `NVARCHAR`
`start_location`	An optional integer expression that specifies where the search begins. If omitted, or if it is less than 1, the search starts at the first character of `string_to_search`.	`INT`

Return Value

Returns an integer representing the starting position of the first occurrence of substring within string_to_search, counting from the start_location. If substring is not found, CHARINDEX returns 0. If substring or string_to_search is NULL, CHARINDEX returns NULL.

Examples

Example 1: Basic Usage

Find the starting position of 'World' in 'Hello World'.


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

Result: 7

Example 2: Specifying Start Location

Find the starting position of 'o' in 'Hello World', starting the search from the 5th character.


                        SELECT CHARINDEX('o', 'Hello World', 5);

Result: 7 (Finds the 'o' in 'World')

Example 3: Substring Not Found

Try to find 'SQL' in 'Hello World'.


                        SELECT CHARINDEX('SQL', 'Hello World');

Result: 0

Example 4: Using with Tables

Find the position of '@' in email addresses in a hypothetical 'Users' table.


                        SELECT Email, CHARINDEX('@', Email) AS AtPosition
                        FROM Users
                        WHERE Email IS NOT NULL;

Tip

CHARINDEX is often used in conjunction with other string functions like SUBSTRING to extract parts of a string based on the position of a delimiter.

Note

The character positions are 1-based. If start_location is specified and it is greater than the length of string_to_search, CHARINDEX returns 0.