SQL Server CHARINDEX() Function

 The CHARINDEX() function in SQL Server is used to find the position of a substring within a string. It returns the starting position of the first occurrence of the substring. If the substring is not found, it returns 0.

Syntax

CHARINDEX(substring, string, start_position)

• substring: The substring to search for.
• string: The string to search within.
• start_position (optional): The position to start the search from. If omitted, the search starts at the beginning of the string.

Return Value

• An integer representing the starting position of the first occurrence of the substring.
• Returns 0 if the substring is not found.

Example Usage

1. Basic Example

SELECT CHARINDEX('SQL', 'SQL Server Tutorial')

Result: 1
Explanation: 'SQL' starts at the first position in the string.

2. Using start_position

SELECT CHARINDEX('e', 'SQL Server Tutorial', 5)

Result: 8
Explanation: The search for 'e' starts at position 5, and the first occurrence after position 5 is at position 8.

3. Substring Not Found

SELECT CHARINDEX('Python', 'SQL Server Tutorial')

Result: 0
Explanation: 'Python' is not found in the string.

4. Case Sensitivity

The CHARINDEX() function is case-insensitive by default, depending on the collation of the database or column. For case-sensitive searches, you would need to use a case-sensitive collation explicitly.

SELECT CHARINDEX('sql', 'SQL Server Tutorial')

Result: 1 (if the collation is case-insensitive).

To enforce case sensitivity:

SELECT CHARINDEX('sql', 'SQL Server Tutorial' COLLATE SQL_Latin1_General_CP1_CS_AS)

Result: 0 (case-sensitive collation).

Notes

• CHARINDEX() starts counting from 1, not 0.
• To find the nth occurrence of a substring, you may need to use it in combination with other string functions like SUBSTRING() or loops in more complex queries.

This function is especially useful for parsing and analyzing strings within SQL queries.