CHARINDEX() in SQL Server with example

 This function searches for one character expression inside a second character expression, returning the starting position of the first expression if found.

CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )

expressionToFind
A character expression containing the sequence to find. Max length 8000 bytes

expressionToSearch
A character expression to search.

start_location
An integer or bigint expression at which the search starts. If start_location is not specified, has a negative value, or has a zero (0) value, the search starts at the beginning of expressionToSearch. This is an optional parameter

Note

• The first position in string is 1.
• If the expressionToFind is not found in expressionToSearch, the CHARINDEX function will return 0.
• Search is not case sensitive

Example:

1) SELECT CHARINDEX('TO', 'Customer') AS MatchPosition;

Result: 4

(Substring is not case sensitive)

2) SELECT CHARINDEX('mer', 'Customer', 3) AS MatchPosition;

Result: 6

3) SELECT CHARINDEX('o', 'Rohit Techvlog', 3) AS MatchPosition;

Result: 13

(Since it started from 3rd position it returns the position of 2nd 'o')

4) SELECT CHARINDEX('z', 'Rohit Techvlog', 1) AS MatchPosition;

Result: 0

(No match found)

5) This function can be used in CASE statement

DECLARE @Name AS VARCHAR(100)= 'Find SQL articles on Rohit techvlog';

SELECT CASE

          WHEN CHARINDEX('rohit', @Name) > 0

           THEN 'Exists'

           ELSE 'Not Exists'

       END AS FindSubString;

Result: Exists

6) This function can be used in a output of existing data in a table.

select JobTitle,CHARINDEX('p', JobTitle) as position_of_p

from Employee

Result: