If you’ve worked with Oracle or MySQL before, you may have used the INSTR() function to find the position of a substring inside a string. But when you’re porting code to SQL Server, you’ll quickly notice that INSTR() isn’t available. Instead, you’ll need to use either CHARINDEX() or PATINDEX(), depending on what you’re trying to do.
The good news is that both functions are pretty straightforward once you know the difference. CHARINDEX() handles simple substring searches, while PATINDEX() adds the ability to use patterns. In this article, we’ll walk through how each one works and how you can swap them in when you’d otherwise use INSTR().
Why Replace INSTR()?
The main reason for replacing INSTR() is that it’s not a T-SQL function and therefore doesn’t exist in SQL Server. If you’re migrating an application or a stored procedure from a different database platform to SQL Server, and that application uses INSTR(), you’ll encounter errors unless you convert any instances of INSTR(). The good news is that SQL Server’s CHARINDEX() and PATINDEX() can be swapped in without too much trouble.
CHARINDEX(): This function is designed to find the starting position of a substring. It’s a direct and efficient replacement for the most common use cases ofINSTR().PATINDEX(): This function provides extra functionality, as it allows you to search for a pattern using wildcard characters, which is a feature not available inINSTR(). This makes it ideal for more complex search scenarios.
Replacing INSTR() with CHARINDEX()
The most straightforward way to replace INSTR() is with CHARINDEX(). This function searches for a specified substring and returns its starting position.
INSTR() Syntax
In MySQL INSTR() works like this:
INSTR(string, substring)In Oracle it works like this:
INSTR(string, substring, [start_position], [occurrence])So we have some extra options.
CHARINDEX() Syntax
The syntax for SQL Server’s CHARINDEX() function goes like this:
CHARINDEX ( substring , string [ , start_position ] )Note that the order of the arguments is reversed. This is important to remember when converting your code.
Example 1: Finding a Substring
Let’s imagine we have a table called ProjectArtifacts with a column ArtifactPath that stores the file paths for various project assets. We want to find the position of the folder “Documentation” in the path.
Let’s first create and populate the table:
CREATE TABLE ProjectArtifacts (
ArtifactID INT PRIMARY KEY,
ArtifactPath NVARCHAR(255),
ResearchNotes NVARCHAR(255)
);
INSERT INTO ProjectArtifacts (ArtifactID, ArtifactPath, ResearchNotes)
VALUES
(1, '/repository/ProjectZ/Documentation/user_manual.pdf', 'Initial project documentation was completed on 2021-03-15.'),
(2, '/repository/ProjectY/documentation/data_analysis_V2.xlsx', 'The study was conducted between the years 2021 and 2023.'),
(3, '/repository/ProjectZ/source/main.py', 'The code base was last updated in 2024.');Here’s an example of code that uses MySQL’s INSTR() function. This will not work in SQL Server:
-- This will NOT work in SQL Server
SELECT INSTR(ArtifactPath, 'Documentation')
FROM ProjectArtifacts
WHERE ArtifactPath = '/repository/ProjectZ/Documentation/user_manual.pdf';Here’s how we can replace the above code using CHARINDEX() in SQL Server:
SELECT CHARINDEX('Documentation', ArtifactPath)
FROM ProjectArtifacts
WHERE ArtifactPath = '/repository/ProjectZ/Documentation/user_manual.pdf';Result:
22
So we essentially replaced INSTR with CHARINDEX and swapped the arguments around.
Example 2: Finding the nth Occurrence
Depending on the DBMS (for example in Oracle), INSTR() can be used to find the starting position of an occurrence after a specific starting point. Fortunately, SQL Server’s CHARINDEX() also has this capability, so this makes it possible to port this functionality over.
Let’s say we have a string with multiple hyphens and we want to find the position of the second hyphen.
Oracle’s INSTR():
SELECT INSTR('B28-PRJ-24A-01-V1', '-', 5) FROM DUAL;SQL Server CHARINDEX():
SELECT CHARINDEX('-', 'B28-PRJ-24A-01-V1', 5);Result:
8
In both cases, we start the search from the 5th character, which is after the first hyphen, so the function correctly returns the position of the second hyphen.
Replacing INSTR() with PATINDEX()
While CHARINDEX() is a great substitute for exact string searches, PATINDEX() is the ideal choice when your search involves pattern matching using wildcards. PATINDEX() allows you to find the first occurrence of a pattern, and its syntax is similar to CHARINDEX().
PATINDEX() Syntax:
SQL Server’s PATINDEX() syntax goes like this:
PATINDEX('%pattern%', string)So it’s basically like CHARINDEX() except that it accepts a pattern instead of a substring.
The pattern must be enclosed in percent signs (%) to indicate that it can be anywhere within the string.
Example 3: Searching for a Pattern
Consider the ProjectArtifacts table again. This time, we want to find the position of any filename that ends with a version number like “V1” or “V2”:
SELECT PATINDEX('%V[0-9]%', ArtifactPath)
FROM ProjectArtifacts
WHERE ArtifactPath = '/repository/ProjectY/documentation/data_analysis_V2.xlsx';Result:
50
Here, V[0-9] is the pattern we are searching for, which matches the letter ‘V’ followed by any single digit from 0 to 9.
Example 4: Using Wildcards
Let’s find the position of any 4-digit year within a description column called ResearchNotes:
SELECT PATINDEX('%[0-9][0-9][0-9][0-9]%', ResearchNotes)
FROM ProjectArtifacts
WHERE ResearchNotes = 'The study was conducted between the years 2021 and 2023.';Result:
43
This demonstrates how PATINDEX() is more versatile than CHARINDEX() and INSTR() for pattern-based searches.
Summary of Differences
| Feature | INSTR() | CHARINDEX() | PATINDEX() |
| Availability | Not in SQL Server | Available in SQL Server | Available in SQL Server |
| Search Type | Literal string | Literal string | Pattern matching with wildcards |
| Argument Order | (string, substring) | (substring, string) | ('%pattern%', string) |
| Use Case | Finding a literal substring | Finding a literal substring | Finding patterns (e.g., specific numbers, alphanumeric sequences) |