In SQL Server, you can use the T-SQL PATINDEX() function to find a pattern within a string. Specifically, the function only returns the first occurrence of the pattern within the string.

The function accepts two arguments; the pattern, and the string.

You can use wildcard characters within the pattern.

Syntax

The syntax goes like this:

PATINDEX ( '%pattern%' , expression )

Where pattern is a character expression that contains the sequence to be found, and expression is the expression to be searched.

Example 1 – Basic Usage

Here’s an example of how it works:

SELECT PATINDEX('%eer%', 'Bob likes beer.') AS Result;

Result:

+----------+
| Result   |
|----------|
| 12       |
+----------+

The percentage sign (%) is a wildcard character that represents any string of zero or more characters.

Example 2 – No Match

Here’s what happens when we don’t include the wildcard characters:

SELECT PATINDEX('eer', 'Bob likes beer.') AS Result;

Result:

+----------+
| Result   |
|----------|
| 0        |
+----------+

Although, if the full string matches the pattern without any wildcards, you’ll get a match:

SELECT PATINDEX('Bob', 'Bob') AS Result;

Result:

+----------+
| Result   |
|----------|
| 1        |
+----------+

However, you won’t get a match if the string contains more characters:

SELECT PATINDEX('Bob', 'Bob likes beer.') AS Result;

Result:

+----------+
| Result   |
|----------|
| 0        |
+----------+

In which case you could use a single wildcard:

SELECT PATINDEX('Bob%', 'Bob likes beer.') AS Result;

Result:

+----------+
| Result   |
|----------|
| 1        |
+----------+

You could also use a single wildcard somewhere in the middle of the pattern:

SELECT PATINDEX('B%r.', 'Bob likes beer.') AS Result;

Result:

+----------+
| Result   |
|----------|
| 1        |
+----------+

In this case we got a match because the start and end of the string matched the start and end of the pattern. Of course, the middle of the string also matched the middle of the pattern, because all other characters are covered by the percentage sign.

Example 3 – The Underscore Wildcard

Here’s another example where we introduce another wildcard character:

SELECT PATINDEX('B_er', 'Beer') AS Result;

Result:

+----------+
| Result   |
|----------|
| 1        |
+----------+

The underscore (_) which is a wildcard for any single character. Therefore, it will only match if there’s exactly one character in that position.

Of course, the above pattern could match many different strings. For example:

SELECT 
  PATINDEX('B_er', 'Beer') AS 'Beer',
  PATINDEX('B_er', 'Bier') AS 'Bier',
  PATINDEX('B_er', 'Baer') AS 'Baer',
  PATINDEX('B_er', 'B er') AS 'B er';

Result:

+--------+--------+--------+--------+
| Beer   | Bier   | Baer   | B er   |
|--------+--------+--------+--------|
| 1      | 1      | 1      | 1      |
+--------+--------+--------+--------+

Example 4 – Comparing the _ and % Wildcards

This example demonstrates the difference between the _ and % wildcard characters.

SELECT 
  PATINDEX('B%r', 'Beer') AS '%',
  PATINDEX('B_r', 'Beer') AS '_',
  PATINDEX('B__r', 'Beer') AS '__';

Result:

+-----+-----+------+
| %   | _   | __   |
|-----+-----+------|
| 1   | 0   | 1    |
+-----+-----+------+

The underscore only matches when there’s exactly one character in that position. To match two characters, we need to use two underscores.

The percentage sign on the other hand, matches any number of characters, including zero, as seen in the following example:

SELECT 
  PATINDEX('Bee%r', 'Beer') AS '%',
  PATINDEX('Bee_r', 'Beer') AS '_',
  PATINDEX('Bee__r', 'Beer') AS '__';

Result:

+-----+-----+------+
| %   | _   | __   |
|-----+-----+------|
| 1   | 0   | 0    |
+-----+-----+------+

Example 5 – A Database Example

Here’s an example of using this function in a database query:

USE Music;
SELECT 
  AlbumName,
  PATINDEX('%the%', AlbumName) AS 'Pattern Index'
FROM Albums
WHERE PATINDEX('%the%', AlbumName) > 0;

Result:

+-------------------------+-----------------+
| AlbumName               | Pattern Index   |
|-------------------------+-----------------|
| Singing Down the Lane   | 14              |
| Ziltoid the Omniscient  | 9               |
| No Prayer for the Dying | 15              |
| The Sixteen Men of Tain | 1               |
+-------------------------+-----------------+

In this case I use the WHERE clause to return only those albums that actually match, along with their PATINDEX() result. If I dropped the WHERE clause, all albums would’ve been returned, regardless of whether they matched or not.

For those that didn’t match, the PATINDEX() result would be zero.

USE Music;
SELECT TOP(10)
  AlbumName,
  PATINDEX('%the%', AlbumName) AS 'Pattern Index'
FROM Albums;

Result:

+-------------------------+-----------------+
| AlbumName               | Pattern Index   |
|-------------------------+-----------------|
| Powerslave              | 0               |
| Powerage                | 0               |
| Singing Down the Lane   | 14              |
| Ziltoid the Omniscient  | 9               |
| Casualties of Cool      | 0               |
| Epicloud                | 0               |
| Somewhere in Time       | 0               |
| Piece of Mind           | 0               |
| Killers                 | 0               |
| No Prayer for the Dying | 15              |
+-------------------------+-----------------+

Example 6 – First Occurrence Only

As mentioned, PATINDEX() returns only the first occurrence of the pattern within the string.

SELECT PATINDEX('%and%', 'Bob and beer and popcorn.') AS Result;

Result:

+----------+
| Result   |
|----------|
| 5        |
+----------+

The CHARINDEX() Function

The T-SQL CHARINDEX() function is similar to PATINDEX(). However, there are some differences between the two. In particular, the PATINDEX() accepts wildcard characters, but not a starting position. CHARINDEX() on the other hand accepts a starting position, but not wildcard characters.