SQL Server: Find Substring in a String with CHARINDEX() Function

We can use CHARINDEX() function to search for a substring in a string, and it will return the position number. If the substring is not found, this function returns 0.

Syntax:

CHARINDEX(substring, string, start)

e.g.

SELECT CHARINDEX('g', 'Pangandaran', 1) AS PositionNumber;

SELECT CHARINDEX('k', 'Pangandaran', 1) AS PositionNumber;

Result:

Start parameter is optional if not filled then by default the search will start from the beginning of the string.

e.g.

SELECT CHARINDEX('d', 'Pangandaran') AS PositionNumber;

SELECT CHARINDEX('d', 'Pangandaran', 1) AS PositionNumber;

Result:

Further, the use of start is to determine the starting point of the start of the search. As we can see in the text "Pangandaran" there are several letters "a". If we search for the position of the letter "a" then the value returned by the CHARINDEX() function is the position of the letter "a" that was first found. Unless we specify the start position (initial) where to start looking.

e.g.

SELECT CHARINDEX('a', 'Pangandaran', 1) AS PositionNumber;

SELECT CHARINDEX('a', 'Pangandaran', 3) AS PositionNumber;

SELECT CHARINDEX('a', 'Pangandaran', 6) AS PositionNumber;

Result:

CHARINDEX() is not case sensitive, upper case or lower case will return the same value. 

e.g.

SELECT CHARINDEX('R', 'Pangandaran', 1) AS PositionNumber;

SELECT CHARINDEX('r', 'Pangandaran', 1) AS PositionNumber;

Result:

CHARINDEX() is not limited to 1 character, but can also be a substring.

e.g.

SELECT CHARINDEX('dar', 'Pangandaran', 1) AS PositionNumber;

SELECT CHARINDEX('an', 'Pangandaran', 1) AS PositionNumber;

SELECT CHARINDEX('an', 'Pangandaran', 3) AS PositionNumber;

Result: