SQL Server CHARINDEX() Function
The CHARINDEX() function in SQL Server helps you find the position of a substring within a larger string. It's a fundamental tool for working with and analyzing text data.
CHARINDEX(): Definition and Usage
CHARINDEX() searches for a substring within a string and returns the starting position of the first match. If the substring is not found, it returns 0. The search is case-insensitive.
Syntax
Syntax
CHARINDEX(substring, string, start)
Parameter Values
| Parameter | Description |
|---|---|
substring |
The substring to search for. This is required. |
string |
The string to search within. This is required. |
start (Optional) |
The starting position for the search (1-based index). If omitted, the search starts at the beginning of the string. |
Examples
Finding the Position of a Substring
This example finds the position of "t" in "Customer".
Syntax
SELECT CHARINDEX('t', 'Customer') AS MatchPosition;
Output
5
Searching for a Substring That Doesn't Exist
Searching for "OM" in "Customer" (no match).
Syntax
SELECT CHARINDEX('OM', 'Customer') AS MatchPosition;
Output
0
Specifying a Starting Position
This searches for "mer" starting from the third position.
Syntax
SELECT CHARINDEX('mer', 'Customer', 3) AS MatchPosition;
Output
4