An Overview of the SQL CHARINDEX function

The SQL CHARINDEX function is used to find the position of a character or substring within a string.

• It returns the substring position in the string from the specified location.

• The function returns the value ‘0’ if the substring is unavailable.

Example 1: The following examples search for the string ‘SQL’ in the string ‘My SQL Server Demo’

SELECT  CHARINDEX('SQL', 'My SQL Server Demo') Stringposition;

If we want to search the substring from a specific position, we can specify the starting position as a third argument. For example, the below SQL CHARINDEX function searches for the substring starting at zero and 10th position. It is helpful if you have multiple substring occurrences and want to skip substring before the starting position.

SELECT 
   CHARINDEX('Hero','Hero, is ultimate Super Hero',0) start_at_zero,
   CHARINDEX('Hero','Hero, is ultimate Super Hero',10) start_at_tenth;

If the substring does not exist in the string, the SQL CHARINDEX function returns zero.

DECLARE @i VARCHAR(100); 
SELECT @i = 'My Name is Rajendra';  
SELECT CHARINDEX('He', @i);