THE WORLD'S LARGEST WEB DEVELOPER SITE
HTML CSS JAVASCRIPT SQL PHP BOOTSTRAP HOW TO JQUERY W3.CSS ANGULARJS XML MORE FORUM EXAMPLES REFERENCES

SQL Server PATINDEX() Function

❮ SQL Server Functions

Example

Return the location of a pattern in a string:

SELECT PATINDEX('%schools%', 'W3Schools.com');
Try it Yourself »

Definition and Usage

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

Note: The search is not case-sensitive.

Syntax

PATINDEX(%pattern%, string)

Parameter Values

Parameter Description
%pattern% Required. The pattern to find. 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. [abc]
  • [^] - Match any character not in the brackets, e.g. [^abc]
string Required. The string to search

Note

  • The first position in string is 1
  • If pattern is not found in string, the PATINDEX() function will return 0

Technical Details

Works in: SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

More Examples

Example

Return the location of a pattern in a string:

SELECT PATINDEX('%s%com%', 'W3Schools.com');
Try it Yourself »

Example

Return the location of a pattern in a string:

SELECT PATINDEX('%[ol]%', 'W3Schools.com');
Try it Yourself »

Example

Return the location of a pattern in a string:

SELECT PATINDEX('%[z]%', 'W3Schools.com');
Try it Yourself »

❮ SQL Server Functions