SQL Server PATINDEX() Function: Searches for String Pattern
In SQL Server, PATINDEX() searches for the first occurrence of a pattern in a given expression and returns its starting position. It returns zero if the pattern is not found.
PATINDEX(pattern, input_string)Parameters
- Pattern: A character expression that is to be found. It must start and end with % character. Wildcard characters like %, _, [], [^] can be used in a pattern.
- input_string: The string in which the pattern is to be searched. It can be a constant string or a table column.
Return Value
Returns bigint if input string is of varchar(max) or nvarchar(max) data type, else returns int.
The starting position for PATINDEX() is NULL. If either pattern or input string is NULL then PATINDEX() returns NULL.
Example 1:
The following example finds the 'llo' in the input string 'Hello! Have a nice day.'. The pattern should be surrounded by %.
SELECT PATINDEX ('%llo%', 'Hello! Have a nice day.') AS PatternPosition
Example 2:
In the following example, we find the pattern 'abc.com' in the Email column of the Employee table. All rows having this pattern in their Email column, return the starting position of the pattern and rows not having this pattern in their Email returns zero.
SELECT Email, PATINDEX('%abc.com%', EMail) AS PatternPosition FROM Employee
Example 3:
The following example uses the wildcard characters % and _ in the searching pattern.
The % matches any string of any length and _ matches for any single character.
SELECT PATINDEX('%ni_e%', 'Hello! Have a nice day.') AS PatternPosition
Example 4:
The following example shows when a pattern is not present in the input string.
SELECT PATINDEX('%world%', 'Hello! Have a nice day.') AS PatternPosition
Example 5:
The following example shows that the PATINDEX() is not case-sensitive.
SELECT PATINDEX('%hello%', 'Hello! Have a nice day.') AS PatternPosition