SQL Server's PATINDEX function is a string function that searches a string for a specified pattern, and returns the starting position of the first occurrence of the pattern within the string. The function takes two arguments: the first argument is the pattern to search for, and the second argument is the string to search within.

Syntax

The syntax for the PATINDEX function is as follows:

PATINDEX ( '%pattern%' , string_expression )

Here, % is used as a wildcard character, and can match any number of characters. For example, the pattern '%cat%' will match any string that contains the characters 'cat', regardless of what characters come before or after them.

The PATINDEX function returns an integer value, which represents the position of the first character of the first occurrence of the pattern within the string. If the pattern is not found within the string, the function returns 0.

Example

Here's an example of using the PATINDEX function to search for a pattern within a string:

SELECT PATINDEX('%at%', 'The cat sat on the mat')

In this example, the function will return the value 4, since the pattern '%at%' first occurs at position 4 within the string 'The cat sat on the mat'.

The PATINDEX function can be useful in a variety of scenarios, such as searching for specific words or characters within a larger string, or extracting specific substrings from a larger string. However, it's important to note that the function is case-sensitive, so if you want to search for a pattern without regard to case, you'll need to use the UPPER or LOWER functions to convert the string to all uppercase or lowercase before performing the search.

See also: T-SQL Functions -> Substring | Replace | Right | Rtrim | Upper