PATINDEX() in SQL

Returns the starting position of the first occurrence of a pattern in a string. Uses pattern matching with wildcards.

Syntax

PATINDEX('%pattern%', string)

Return type

INTEGER

PATINDEX() Function Example

SELECT PATINDEX('%world%', 'Hello world');
-- Output: 7

What is PATINDEX() in SQL?

The PATINDEX() function in SQL returns the starting position of the first occurrence of a specified pattern within a given string. Unlike CHARINDEX(), which searches for an exact substring, PATINDEX() allows pattern matching with wildcards, making it more flexible for locating text fragments. It is a 1-based index function (starting position is 1) and is primarily supported in SQL Server. If the pattern is not found, PATINDEX() returns 0. This function is particularly useful for searching within unstructured text, validating data formats, and extracting dynamic patterns in SQL queries.

Parameters:

pattern: Pattern to search for (must include wildcards %)
string: The string to search within

Example Use Cases:

-- Find position of first number

SELECT PATINDEX('%[0-9]%', 'ABC123DEF') AS first_number_pos;
-- Output:
first_number_pos
---------------
4

-- Find position of word starting with 'S'

SELECT PATINDEX('S%', product_name) AS s_word_pos FROM products;
-- Output:
s_word_pos
----------
1
5
1
0
1

-- Locate email pattern

SELECT PATINDEX('%@%.%', email) AS email_pattern_pos FROM users;
-- Output:
email_pattern_pos
----------------
9
6
10
8
5

Notes:

Behavior: Returns 0 if the pattern is not found, requires wildcards (%) in the pattern, and is case-insensitive by default.
Performance Considerations: More resource-intensive than simple CHARINDEX
Version Info: Primarily SQL Server function
Deprecated/Recommended Alternatives: REGEXP_INSTR for other DBMS