SQL PATINDEX Function

Example

Return the position of a pattern in a string:

SELECT PATINDEX(‘%schools%’, ‘iampsp.com’);

Definition and Usage

The PATINDEX() function returns the position of a pattern in a string.

If the pattern is not found, this function returns 0.

Note: The search is case-insensitive and the first position in string is 1.

Syntax

PATINDEX(%pattern%, string)

Parameter Values

Parameter Description
%pattern% Required. The pattern to find. It MUST be surrounded by %. Other wildcards can be used in pattern, such as:

  • % – Match any string of any length (including 0 length)
  • _ – Match one single character
  • [] – Match any characters in the brackets, e.g. [xyz]
  • [^] – Match any character not in the brackets, e.g. [^xyz]
string Required. The string to be searched

Technical Details

Works in: SQL Server (starting with 2008), Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse

More Examples

Example

Return the position of a pattern in a string:

SELECT PATINDEX('%s%com%', 'iampsp.com');

Example

Return the position of a pattern in a string:

SELECT PATINDEX('%[ol]%', 'iampsp.com');

Example

Return the position of a pattern in a string:

SELECT PATINDEX('%[z]%', 'iampsp.com');