SQL Server CHARINDEX() Function (Transact-SQL) : Complete Overview With Examples

Vijay Kashyap Algo Trading Expert

CHARINDEX() in Function: Advanced functions in SQL Server

CHARINDEX() is a handy tool for working with strings and searching through columns. This article will break down CHARINDEX(), covering its basics, how it's used, and providing real-life examples.

What Is CHARINDEX()?

The CHARINDEX() function in SQL Server is used to find the starting position of a substring within a string. It returns the position of the first occurrence of a specified substring in a given string.

Syntax:

    CHARINDEX(substring, string, start_position);

'substring': The substring to search for within the main string.
'string': The main string in which to search for the substring.
'start_position' (optional): The position in the main string to start the search. If omitted, the search starts from the beginning of the main string.

Let's Create a Table and Understand Some Examples of CHARINDEX():

CREATE TABLE SampleTable
(
    ID INT PRIMARY KEY,
    Description VARCHAR(100)
);

INSERT INTO SampleTable (ID, Description)
VALUES
(1, 'Hello, this is an example text.'),
(2, 'SQL Server is powerful and versatile.'),
(3, 'Let''s explore the CHARINDEX() function.');



Example 1: Find the position of the word 'example' in the Description column

SELECT Description, CHARINDEX('example', Description) AS Position
FROM SampleTable;

Result:


Example 2: Find the position of the word 'SQL' in a case-insensitive manner.

SELECT Description, CHARINDEX('SQL', Description) AS Position
FROM SampleTable;

Result:



Conclusion:

The CHARINDEX() function in SQL Server is a versatile tool for string manipulation and searching. Its ability to find the position of a substring within a string makes it a valuable asset for various scenarios in database querying.

In this blog post, we've covered the basic syntax of CHARINDEX(), explored its practical applications, and provided examples to illustrate its usage. Whether you're searching for specific patterns in text columns or extracting positional information, CHARINDEX() is a function worth mastering in your SQL Server journey.