CHARINDEX SQL Server function

In SQL Server (Transact-SQL), the CHARINDEX function returns the substring location in a string. The search is case insensitive.

Syntax of the CHARINDEX function in SQL Server (Transact-SQL):

CHARINDEX( substring, string, [start_position] )

CHARINDEX function parameters or arguments in SQL Server (Transact-SQL)

• It’s a substring you want to find.
• string – the string in which the search is performed.
• start_position – optional. The position in the string, from which the search will start.

Note:

1. The first position in the line is 1.
2. If the substring is not found in the string, the CHARINDEX function will return 0.

Application:
The CHARINDEX function can be used in the following versions of SQL Server (Transact-SQL):
SQL Server vNext, SQL Server 2016, SQL Server 2015, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

Example:
Consider some examples of the CHARINDEX SQL Server function to understand how to use the CHARINDEX function in SQL Server (Transact-SQL). For example:

SELECT CHARINDEX('s', 'SQL Server');
--Result: 1 (the search is case insensitive, so it will match 'S')

SELECT CHARINDEX('s', 'SQL Server', 2);
--Result: 5

SELECT CHARINDEX('ER', 'SQL Server');
--Result: 6 (the search is case insensitive, so it will match 'er').

SELECT CHARINDEX('x', 'SQL Server');
--Result: 0

The CHARINDEX function has syntax:

CHARINDEX( search_expression, string_expression [, start_position ])

Here the optional integer parameter start_position defines the position in the string expression, starting from which the search is performed. If this parameter is omitted, the search is performed from the beginning of the string_expression.

For example, the query:

SELECT name
FROM Ships
WHERE CHARINDEX('sh', name) > 0;

will output those ships that have a combination of “sh” characters. It uses the fact that if the sought string is not found, the CHARINDEX function returns 0. The result of the query will contain the following ships:

It should be noted that if the sought substring or string expression is NULL, the result of the function will also be NULL.

The following example defines the position of the first and second occurrence of the symbol a in the ship name California:

SELECT CHARINDEX('a',name) first_a,
CHARINDEX('a', name, CHARINDEX('a', name)+1) second_a
FROM Ships
WHERE name='California';

Note that when defining the second character, the CHARINDEX function uses the starting position, which is the position following the first letter of a character – CHARINDEX(‘a’, name)+1. The correctness of the result – 2 and 10 – is easy to check.