T-SQL 101: 50 Using CHARINDEX and PATINDEX to find one string in another in SQL Server

It’s useful to be able to find one string within another string. T-SQL has two basic functions for that: CHARINDEX and PATINDEX.

CHARINDEX is used to see if one string appears within another string. Take a look at this code:

In this first example, I asked SQL Server to look for the string lot in the string Hellothere. You can see that it found it at position 4.

But the second string lod wasn’t found, so it returned position 0 to indicate that it could not find it.

Note that there is an option additional parameter that tells you which character position to start looking at.

PATINDEX

PATINDEX is a bit more complex:

PATINDEX is basically “pattern index”. We define a pattern that we’re looking for and it returns the position of the first place where it occurs. The patterns are the ones we saw in the LIKE operator previously.

% is a wildcard that matches anything or nothing, ^ says “not” and anthign in square brackets is a list of values, and you can also include a range like I did in this example, where I asked for any character between 0 and 4.

The first digit was 5 but that’s not between 0 and 4, so it returns position 6 which is there the 4 character is located within the string.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2019-12-30