CHARINDEX function in SQL Server

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 is used to 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 :

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

Note :

1. The first position in the series is 1.
2. If no substring is found in the string, CHARINDEX will return 0.
3. 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.

For 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 

Previous article: Function CHAR in SQL Server

Next lesson: CONCAT function in SQL Server