How to use CHARINDEX in Sql Server

Intro

The SQL Server CHARINDEX function will search for a substring inside a string and return it's position. In this article, we will learn how to use CHARINDEX in SQL Server.

The Syntax

The basic syntax of a CHARINDEX is as follows:

CHARINDEX(substring, string [, start_location])

The parameters are as follows:

• substring: the string to be search for
• string: the string to search
• start_location: the position to begin the search

Getting Setup

For this, we will be using docker. This is recommended for more than just using SQL Server. To find how to install docker go here: https://docs.docker.com/engine/install/

Now create a file called docker-compose.yml and add the following.

version: "3.9"
services:
  db:
    image: "mcr.microsoft.com/mssql/server"
    ports: 
      - 1433:1433
    environment:
        SA_PASSWORD: "Your_password123"
        ACCEPT_EULA: "Y"

Open a terminal and go to the folder the file is located. Then run the following.

docker-compose up

If you are looking for another good reference, you can check here: https://docs.docker.com/samples/aspnet-mssql-compose/.

Basic Example

Our first example shows how to search the string "hello world" for the string "word". We see the position of the word is at index 7.

select charindex('world', 'hello world') as position;

Case Sensitivity

One thing to note is be default, the function is case insensitive.

select charindex('world', 'hello world') as position;

If we want the string to be case sensitive, we can specify a COLLATE.

select charindex('world', 'hello WORLD' COLLATE Latin1_General_CS_AS) as position;

As a side note, you can learn more about collate below and select all your available collates using the following query.

• https://docs.microsoft.com/en-us/sql/t-sql/statements/collations?view=sql-server-ver16

SELECT name, description
FROM fn_helpcollations();

Using a position

For the final example, let's add a start position to search. Here we specify 2, so the word searched will be llo world.

select charindex('world', 'hello world', 2) as position;

The above doesn't change the result. However, if we restrict the search to orld, we will not find the sub string.

select charindex('world', 'hello WORLD', 8) as position;