Free Tools

Resources

Blog

Docs

Pricing

Book a Demo

Get Free Extension

Get Sherloq Free

Back to Library

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

Error Handling:

Error: Returns NULL if either parameter is NULL
Recommendation: Use COALESCE for NULL handling if needed

Supported Databases:

DBMS

Function / Syntax

Example

Behavior with NULL

MySQL

REGEXP_INSTR

REGEXP_INSTR(str, pattern)

Returns NULL

PostgreSQL

SIMILAR TO

SIMILAR TO pattern

Returns NULL

SQL Server

PATINDEX

PATINDEX('%pat%', str)

Returns NULL

SQLite

REGEXP

REGEXP pattern

Returns NULL

BigQuery

REGEXP_INSTR

REGEXP_INSTR(str, pattern)

Returns NULL

Snowflake

REGEXP_INSTR

REGEXP_INSTR(str, pattern)

Returns NULL

Athena

REGEXP_INSTR

REGEXP_INSTR(str, pattern)

Returns NULL

Related Functions:

CHARINDEX()

Returns the starting position of a substring within a string. Position is 1-based (first character is position 1).

SUBSTRING()

Extracts a portion of a string based on specified position and length.

REPLACE()

Replaces all occurrences of a substring within a string with another substring.

REVERSE()

Returns a string value in a reversed order (characters displayed in reverse order).

STUFF()

Deletes a specified length of characters and inserts another string at a specified start position in a string.

PATINDEX()

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

RIGHT()

Extracts a specified number of characters from the end (right side) of a string.

LEFT()

Extracts a specified number of characters from the beginning (left side) of a string.

LENGTH()/LEN()

Returns the number of characters in a string. Note: Function name varies by DBMS (LENGTH in most, LEN in SQL Server).

TRIM()

Removes leading and/or trailing spaces (or specified characters) from a string.

LOWER()

Converts all characters in a string to lowercase letters.

UPPER()

Converts all characters in a string to uppercase letters.

CONCAT()

Concatenates two or more strings into a single string.

One place for all your queries,
directly on your SQL editor

Get Free Extension

Book a Demo

Get Sherloq Free

PATINDEX() in SQL

Syntax

Return type

PATINDEX() Function Example

What is PATINDEX() in SQL?

Parameters:

Example Use Cases:

Notes:

Error Handling:

Supported Databases:

DBMS

Function / Syntax

Example

Behavior with NULL

MySQL

PostgreSQL

SQL Server

SQLite

BigQuery

Snowflake

Athena

Related Functions:

CHARINDEX()

SUBSTRING()

REPLACE()

REVERSE()

STUFF()

PATINDEX()

RIGHT()

LEFT()

LENGTH()/LEN()

TRIM()

LOWER()

UPPER()

CONCAT()

One place for all your queries, directly on your SQL editor

Explore

Resources

Free Tools

Legal

Explore

Resources

Free Tools

Legal

Explore

Resources

Free Tools

Legal

One place for all your queries,
directly on your SQL editor