Beginner

104. PATINDEX Function

Learn how PATINDEX can locate a pattern within a string in SQL Server.

📘 Lesson 104 of 728 ⏱ 8 min 🎯 Intermediate

PATINDEX is a SQL Server function that returns the starting position of the first occurrence of a pattern in a string. It supports wildcard patterns.

PATINDEX is useful when searching for patterns rather than only an exact text value.

An analyst can use PATINDEX to identify the position of the first numeric character in a text value.

SQL 
SELECT
    CustomerCode,
    PATINDEX('%[0-9]%', CustomerCode) AS NumberPosition
FROM Customers;

PATINDEX can be useful for data cleaning, pattern detection, text validation, and parsing semi-structured data in SQL Server.

  • PATINDEX searches for patterns.
  • It returns the starting position of a matching pattern.
  • It supports wildcard patterns in SQL Server.
  • It is useful for text validation.
  • It differs from CHARINDEX because it supports patterns.
What is PATINDEX?

PATINDEX is a SQL Server function that returns the starting position of a specified pattern within a string.

How is PATINDEX different from CHARINDEX?

PATINDEX supports pattern matching, while CHARINDEX is primarily used to find a specified expression.

Have you finished this lesson?

Introduction to SQL – All Lessons