CHARINDEX() returns the starting position of an expression in another expression. It takes below arguments:

CHARINDEX ( exp1, exp2, position )

Where, exp1 = expression to be searched,

exp2 = the main expression which contains exp1,

position = this is optional, it specifies a position in exp2 from which the search for exp1 starts.

CHARINDEX() returns and INT/BIGINT based on the data type of exp2, which specifies the starting point of exp1. For data types declared with (MAX) it returns BIGINT.

-- © 2011 – Vishal (http://SqlAndMe.com)
 
DECLARE @string VARCHAR(128)
SET @string =     'CHARINDEX searches for the string for the ' +
                  'first occurrence of a specified character/string'
SELECT      CHARINDEX('Char', @string)    AS 'Position',
            CHARINDEX('Char', @string, 2) AS 'Position'

Result Set:

Position    Position
———–        ———–
1           75
 
(1 row(s) affected)

In the above example, the first columns returns 1 as the string started with 'CHAR…', while in the second column the search was started from 'HARINDEX…', which resulted in returning the second occurrence of 'CHAR'.

Searches are based on the collation of the data. CHARINDEX() has performed a case-insensitive search as my current collation is set to Latin1_General_CI_AI. To perform a case-sensitive search here we can apply Latin1_General_CS_AI collation to @string:

DECLARE @string VARCHAR(128)
SET @string =     'CHARINDEX searches for the string for the ' +
                  'first occurrence of a specified character/string'
SELECT      CHARINDEX('char', @string COLLATE Latin1_General_CS_AI)
            AS 'Position'

Result Set:

Position
———–
75
 
(1 row(s) affected)

In this example, the first occurrence of 'Char' is ignored as it does not match the case.

Hope This Helps! Cheers!