How PATINDEX works in SQL? Best PATINDEX examples

How PATINDEX works in SQL? Best PATINDEX examples

If you’ve ever worked with SQL Server, you might have come across the need to find the position of a specific pattern inside a string. This is where the PATINDEX function comes into play. It’s a powerful yet often overlooked function that can make searching within text data much easier. In this article, I’ll break down how PATINDEX works, give some practical examples, and compare it to other SQL string functions.

What is PATINDEX?

PATINDEX is a built-in SQL Server function that allows you to find the starting position of a specific pattern inside a string. Unlike some other string functions, PATINDEX supports wildcard characters, making it handy for flexible pattern matching.

Syntax of PATINDEX

Here’s the basic syntax for PATINDEX:


PATINDEX('%pattern%', expression)

Where:

  • %pattern% – The pattern you are searching for. This must include wildcards like % to indicate variable text.
  • expression – The text string in which to search for the pattern.

How PATINDEX Works in SQL?

To better understand PATINDEX, let’s look at a few examples. Suppose we have a table with customer reviews, and we want to find occurrences of the word “great”.

Basic Example

Let’s retrieve the starting position of the word “great” in a sample text:


SELECT PATINDEX('%great%', 'This is a great product!') AS Position;

Output:

Position
11

The function returns 11 because “great” starts at the 11th character in the string.

Using PATINDEX with Wildcards

The real power of PATINDEX is seen when using wildcards. Let’s find the position of a word that starts with “gr”.


SELECT PATINDEX('%gr%t%', 'This is a great product!') AS Position;

Output:

Position
11

Even with wildcards, the function correctly identifies the starting position.

Difference Between PATINDEX and CHARINDEX

At this point, you might be wondering how PATINDEX differs from CHARINDEX. Let’s break it down.

Function Supports Wildcards? Case Sensitivity Use Case
PATINDEX Yes Depends on collation Searching with wildcard patterns
CHARINDEX No Depends on collation Finding exact text matches

Advanced Usage of PATINDEX

Using PATINDEX in WHERE Clause

You can use PATINDEX in a WHERE clause to filter records based on a pattern match. Suppose we want to find all customer reviews mentioning “fast”:


SELECT * FROM CustomerReviews WHERE PATINDEX('%fast%', review_text) > 0;

Removing Non-Alphanumeric Characters

PATINDEX is useful for data cleaning. Here’s an example of removing non-alphanumeric characters from a string:


SELECT 
  SUBSTRING(myColumn, PATINDEX('%[A-Za-z0-9]%', myColumn), LEN(myColumn)) 
FROM myTable;

This removes leading special characters from a given column.

Limitations of PATINDEX

While PATINDEX is powerful, it has some limitations:

  1. It only works with string data types (char, varchar, text, etc.).
  2. If the pattern does not exist in the string, it returns 0 instead of NULL.
  3. Can be slower for large datasets compared to indexed searches.

Conclusion

The PATINDEX function is a valuable tool when working with pattern matching in SQL Server. Its ability to leverage wildcards makes it much more flexible compared to CHARINDEX. Whether you’re filtering data, cleaning text, or performing advanced searches, knowing how PATINDEX works in SQL will definitely improve your query efficiency.

 

Other interesting article:

How REGEXP works in SQL? Best REGEXP examples