CHARINDEX Function in SQL Server: Explained

The article will learn and show you how to use the CHARINDEX () function in SQL Server to find the location of a substring within the big parent string.

Describe

The CHARINDEX function in SQL Server helps search for a substring within a large string starting from the specified position. The function returns the result where the substring is found or returns 0 if not found. Chain positions start at 1, do not start from 0 as in other cases.

Syntax

To run CHARINDEX function in SQL Server, we use the following syntax:

 CHARINDEX( chuoi_con, chuoi_cha, [vi_tri_bat_dau]) 

Parameters :

• chuoi_con : the substring you want to search. Its length is limited to 8000 characters.
• chuoi_cha: string to search, can be a text string, expression or column.
• vi_tri_bat_dau: Not required. The position in the chain where the search will begin. The first position is 1.

Note :

• The first position in the series is 1.
• If no substring is found in the string, CHARINDEX will return 0.
• CHAR function can only be used in later versions of SQL Server: SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005.

As an example,

Take a look and explore some examples of CHARINDEX functions in SQL Server.

 SELECT CHARINDEX('q', 'TipsMake.com'); 
 Result: 1 (tìm ki?m không phân bi?t ch? hoa ch? th??ng) 
 
 SELECT CHARINDEX('n', 'TipsMake.com', 2); 
 Result: 4 
 
 SELECT CHARINDEX('n', 'TipsMake.com', 6); 
 Result: 10 
 
 SELECT CHARINDEX('AN', 'TipsMake.com'); 
 Result: 3 (tìm ki?m không phân bi?t ch? hoa ch? th??ng) 
 
 SELECT CHARINDEX('an', 'TipsMake.com', 7); 
 Result: 9 (search is not case-sensitive so it will match on 'On') 
 
 SELECT CHARINDEX('z', 'TipsMake.com'); 
 Result: 0 

Next lesson: CONCAT function in SQL Server