How to use PATINDEX in Sql Server

Intro

SQL Server provides the PATINDEX function to return the first occurrence of a patter in a string. In this article, we will learn how to use PATINDEX in SQL Server.

The Syntax

The basic syntax of a PATINDEX is as follows:

SELECT PATINDEX('%pattern%', string);

• pattern: Is a character list where you can also use wildcards such as % and _
• string: Is the string you would like to 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

A basic example involves searching a string literal using a sub string.

select patindex('%world%', 'Hello World') position;

This indicates that a substring matching our pattern starts a index 7.

Using Wildcards

In our next examples, we will use the wild cards % and _ to match a substring.

The _ character will match a string missing one character in the specified position.

select patindex('%w_rld%', 'Hello World') position;

The % character will match many characters missing. Here we match anything that starts with w and ends with d.

select patindex('%w%d%', 'Hello World') position;