SQL Server: CHARINDEX Function

This SQL Server tutorial explains how to use the CHARINDEX function in SQL Server (Transact-SQL) with syntax and examples.

Description

In SQL Server (Transact-SQL), the CHARINDEX functions returns the location of a substring in a string. The search is NOT case-sensitive.

Syntax

The syntax for the CHARINDEX function in SQL Server (Transact-SQL) is:

CHARINDEX( substring, string, [start_position] )

Parameters or Arguments

substring

The substring that you want to find.

string

The string to search within.

start_position

Optional. The position in string where the search will start. The first position is 1.

Note

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

Applies To

The CHARINDEX function can be used in the following versions of SQL Server (Transact-SQL):

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

Example

Let’s look at some SQL Server CHARINDEX function examples and explore how to use the CHARINDEX function in SQL Server (Transact-SQL).

For example:

SELECT CHARINDEX('t', 'TechOnTheNet.com');
Result: 1          (search is not case-sensitive so it will match on 'T')

SELECT CHARINDEX('t', 'TechOnTheNet.com', 2);
Result: 7

SELECT CHARINDEX('t', 'TechOnTheNet.com', 8);
Result: 12

SELECT CHARINDEX('ON', 'TechOnTheNet.com');
Result: 5          (search is not case-sensitive so it will match on 'On')

SELECT CHARINDEX('z', 'TechOnTheNet.com');
Result: 0