CHARINDEX() function SQL Server

CHARINDEX() :
This function in SQL Server helps to return the position of a substring within a given string. The searching performed in this function is NOT case-sensitive.

Syntax :

CHARINDEX(substring, string, [starting_position]

Parameters :
This function accepts 3 parameters.

1. substring –
   The substring that we are searching for. It has a limit of 8, 000 characters.
2. string –
   The string in which searching takes place.
3. starting_position –
   The position from where searching will take place. It’s an optional parameter.

Returns :

• The function will return the position of a substring within a given string.
• If the substring is not found in the string, then the function will return 0.

Applicable to the following versions :

• SQL Server 2017
• SQL Server 2016
• SQL Server 2014
• SQL Server 2012
• SQL Server 2008 R2
• SQL Server 2008
• SQL Server 2005

Example-1 :
Searching a character using the CHARINDEX() function.

SELECT CHARINDEX('k', 'GeeksforGeeks') 
As Found ;

Output :

Found
4

Example-2 :
Searching a substring using the CHARINDEX() function.

SELECT CHARINDEX('fully', 'Life is a journey so live it fully') 
As Found ;

Output :

Found
30

Example-3 :
If the substring doesn’t match with the given string.

SELECT CHARINDEX
('python', 'Geeks for geeks is a well known computer science website') 
As Found ;

Output :

Found
0

Example-4 :
Use of “starting_position” parameter in CHARINDEX() function.

SELECT CHARINDEX
('for', 'Love for all, Hate for none', 10) 
As Found ;

Output :

Found
20

Example-5 :
Showing that CHARINDEX() function is case-insensitive.

SELECT  
CHARINDEX('Bear', 'Bob likes Bear, beer likes bob') 
As Found1,
CHARINDEX('bear', 'Bob likes Bear, beer likes bob') 
As Found2 ;

Output :

Found1	Found2
11	11

Example-6 :
Making the function case-sensitive using the COLLATE clause.

SELECT CHARINDEX
('A', 'There is always a need to Develop' COLLATE Latin1_General_CS_AS) 
As Found;

Output :
The function is now following a case-sensitive search, and since it doesn’t contain “A”, so the function will return 0.

Found
0