How to Use SQL Server CHARINDEX() Function

Developers often find it slow and difficult to search for specific parts of a string in SQL. Searching through large text can be time-consuming and make work more complicated.

SQL Server CHARINDEX function helps by quickly finding the position of a SQL Server substring. This makes it easier and faster to work with text in SQL.

In this guide, we’ll explain how CHARINDEX() works, highlight its uses, and walk you through a few simple examples to help you grasp it easily. If you want to improve your string-handling skills, this guide will help you use CHARINDEX() with confidence.

Table of contents

• What is the SQL Server CHARINDEX function?
• Parameters of CHARINDEX
• Examples of using CHARINDEX() in SQL Server
• Try it yourself with dbForge Studio
• Case sensitivity in CHARINDEX()
• Using the optional start position parameter with CHARINDEX
• Further learning
• FAQ

What is the SQL Server CHARINDEX function?

In the world of SQL Server, functions like PATINDEX and CHARINDEX help developers find the position of a substring in a string. However, their main difference lies in the match specifics. SQL server PATINDEX allows pattern matching with wildcards, and CHARINDEX looks for an exact match, i.e., the first occurrence of a substring in a string. What is the SQL server CHARINDEX() function? The CHARINDEX function in SQL Server helps you define the substring position in a string. If the SQL Server substring is present, it returns the starting position as a number. If it’s not found, the function returns 0.

Syntax

CHARINDEX (substring, string, start)

• substring – The text you’re searching for.
• string – The main text where you’re looking for the substring function SQL server.
• start (optional) – The position in the string where the search starts (default is 1). 

How it works

CHARINDEX SQL Server works like a scanner. It checks the string from left to right. By definition, it treats uppercase or lowercase letters as the same.

Example

SELECT CHARINDEX('SQL', 'Learning SQL is fun!', 1);

This returns 10 because “SQL” starts at position 10 in the sentence.

Parameters of CHARINDEX

The CHARINDEX() function has three parameters to help you find a substring in a string.

• substring – The text you want to find with the substring SQL server function.
• string – The main text where you’re searching.
• start (optional) – The position in the string where the search begins. If you don’t specify another starting point, it starts from the beginning (position 1).

Let’s take a look at how the parameters of CHARINDEX work. 

Here’s an example:

SELECT CHARINDEX('is', 'This is a crisis.');

As you can see, there are 4 “is” in the string. If we use this query without parameters, it will find “is” in “This” (the first occurrence) and will return “3”.
However, if we’re going to use the following query with a parameter added to its end:

SELECT CHARINDEX('is', 'This is a crisis.', 4);

It will find “is” in “is” at position “6”, but won’t locate it in other parts of the string containing this combination of characters. 

Examples of using CHARINDEX() in SQL Server

As we have found out, the CHARINDEX() function assists in finding the position of a substring inside a string. This is a so-called substring function in SQL server. It returns a number showing where the SQL Server substring starts. If the substring isn’t found, it returns 0. 

Finding a word

SELECT CHARINDEX('SQL', 'Learning SQL is fun!');

Result: 10 (because “SQL” starts at position 10).

Using the start position

SELECT CHARINDEX('o', 'Hello World', 6);

Result: 8 (starts searching from position 6 and finds “o” in “World”).

Column search

In real database queries, CHARINDEX in SQL server is often used to search within table columns. Here’s an example:

DECLARE @Employees TABLE (Name NVARCHAR(100));
INSERT INTO @Employees (Name) VALUES
('Johnathan Smith'),
('Alice Johnson'),
('Olivia Benson');

Using this query, we have inserted three values into the table. Now, let’s use a query to find if there’s “John” found in the name or surname of any of the employees. 

SELECT Name, CHARINDEX('John', Name) AS Position
FROM @Employees;

This query will look for “John” to appear in each employee’s name. If “John” isn’t found (e.g., in “Olivia Benson”), the query will return 0.

Here’s an output:

Name	Position
Johnathan Smith	1
Alice Johnson	7
Olivia Benson	0

Replacing the first occurrence of a substring

The REPLACE() function in SQL Server replaces all occurrences of a SQL Server SQL substring, but if you need to replace only the first occurrence, you can use STUFF() and CHARINDEX().

How it works

• CHARINDEX() finds where the substring in SQL server appears.
• STUFF() removes the substring and inserts a new one in its place.

STUFF() and CHARINDEX() help with precise string modifications. They work well for targeted replacements but can make queries more complex, especially for large datasets. Learn more about this use case here.

From these examples, you can see how SQL Server CHARINDEX helps you to easily search for text in strings, filter data, and manipulate values in SQL Server.

Try it yourself with dbForge Studio

Want to test CHARINDEX() and other SQL functions in a more user-friendly environment? dbForge Studio for SQL Server is a great alternative to SSMS, offering extra features to make your work easier.

Why use dbForge Studio instead of SSMS?

While SSMS is the default tool for SQL Server, dbForge Studio provides:

• Smarter SQL coding – Autocomplete, formatting, and debugging help speed up your work.
• Visual query builder – Create queries without the necessity to write complicated SQL.
• Easy data comparison – Quickly find and sync differences between databases.
• Performance tracking – Detect slow queries and optimize them.

Check out this comparison of dbForge Studio vs. SSMS to see the full benefits.

See it in action

Watch this YouTube video to learn how dbForge Studio can improve your workflow.

Get started

dbForge Studio for SQL Server is a handy platform with quick and easy onboarding. You should just follow the basic steps to set up your environment and start working with SQL.

Download dbForge Studio for the latest version. Install it with the step-by-step guide. Try features like the query builder, debugging tools, and performance monitoring. Write queries using the SQL editor with autocomplete and formatting.

Case sensitivity in CHARINDEX()

In SQL Server, the CHARINDEX() function doesn’t consider substring SQL server cases by default. As we’ve mentioned before, it treats uppercase and lowercase letters the same.

Case-insensitive search (Default)

SELECT CHARINDEX('sql', 'Learning SQL is fun!');

Result: 10 (finds “SQL” even though the case doesn’t match).

Making searches case-sensitive

If your task is a case-sensitive search, you can use the COLLATE clause like SQL_Latin1_General_CP1_CS_AS.

SELECT CHARINDEX('sql', 'Learning SQL is fun!' COLLATE SQL_Latin1_General_CP1_CS_AS);

Result: 0 (because “sql” in lowercase doesn’t match “SQL” in uppercase).

Case-sensitive example with a table

Let’s assume that we have added six new employees to our table:

DECLARE @Employees TABLE (Name NVARCHAR(100));
INSERT INTO @Employees (Name) VALUES
('Johnathan Smith'),
('Alice Johnson'),
('john Doe'),
('Olivia Benson'),
('JOHN DOE');

Now let’s check for “john” in lowercase using COLLATE clause for case sensitivity. 

SELECT
Name,
CHARINDEX('john', Name COLLATE SQL_Latin1_General_CP1_CS_AS) AS Position
FROM
@Employees;

Since the collation is case-sensitive, only names that exactly match ‘john’ in lowercase will return a position greater than 0.

Here’s our test output:

Name	Position
Johnathan Smith	0
Alice Johnson	0
john Doe	1
Olivia Benson	0
JOHN DOE	0

Using the optional start position parameter with CHARINDEX

The CHARINDEX function in SQL server has an optional start position. You can choose where to begin searching in the string.

How the start position works

The start position parameter is responsible for telling CHARINDEX() where to start its search. You give a number, and it starts from that string point.

Example 1: Starting search from a specific position

SELECT CHARINDEX('o', 'Hello World', 6);

Result: 8

As you can tell, the first ‘o’ appears at position 5, but the search starts at position 6.

Looking from position 6, the next occurrence of ‘o’ is at position 8.

Example 2: Searching for recurring substrings

For example, if you want to find the second occurrence of a character or substring in SQL server substring function, you can set the start position to skip the first match.

SELECT CHARINDEX('o', 'Hello World, Hello Again', 12);

Result: 17
Since the search starts at position 12, the next occurrence of ‘o’ after position 12 is at position 17 (‘o’ in the second ‘Hello’).

You can use the SQL substring to extract a part of a string from a specific position. This way, you can detect repeated characters.

Further learning

Want to learn more about SQL Server and dbForge Studio? We have some helpful resources for you to dive into:

• SQL Server Tutorials;
• Detailed help and instructions in dbForge Studio Documentation; 
• Practicable dbForge Studio Video Tutorials; 
• Courses and resources to further develop your SQL skills in Devart Academy.

These materials will help you become more comfortable and skilled with SQL Server and dbForge Studio. 

FAQ