The SQL PATINDEX function searches an input expression for a given pattern. It returns the starting index of the first occurrence of the pattern found in the input expression.
Description
The PATINDEX function is very similar to the LIKE function in SQL. However, it returns the starting position of the first occurrence of the pattern in the input string. The search is case-sensitive. The PATINDEX starts indexing from position 1.
SQL PATINDEX Usage Notes
The first argument in the PATINDEX function is the pattern to be searched in the input string. The second argument is the input expression to be searched for the given pattern. The data type of the pattern and input expression is of character string category.
If the pattern is not present, the PATINDEX function returns 0. If the input expression or the pattern is NULL, the PATINDEX function returns a NULL. The maximum limit of characters in a pattern is 8000.
If the data type of the input expression is varchar(max) or nvarchar(max), then the return data type is bigint, otherwise int.
The pattern encloses in %%. We can use Wildcards in the pattern like,
- an underscore _ to match a single character
- braces [] to match any character in the square brackets
- [^] to match any character, not in the square brackets
- percent % to match any string of any length
Syntax
PATINDEX (‘%pattern%’, input_expression)
SQL PATINDEX Examples
The following example shows a simple use of the PATINDEX function.
The above query returns the starting index of the pattern Index in the input string.
The following queries use the AdevnetureWorks2019 database.
We can use the PATINDEX function to find a specific value in the column.
The above query returns the starting position of the word alloy in the product description column. The function returns zero if there is no alloy word in the product description value.
The following query shows how to check an unwanted value in the database column.
The PATINDEX function checks for a pattern that is not an alphabet, number, or underscore. The valid carrier tracking number comprises alphanumeric values separated by an underscore.
The following example uses a wildcard with the pattern.
The result shows the staring index of pattern ‘Dis_’. The underscore (_) symbol matches all the patterns that start with Dis. The output contains all special offers on a discount.
