PATINDEX

SQL Server

PATINDEX(‘%pattern%’, expression)

• ‘%pattern%’: This parameter represents the specific pattern to be searched for in the expression. The pattern should be specified as a string and can include wildcard characters.
• expression: This refers to the character-based expression where the ‘%pattern%’ string is searched. It can be a character or binary data type.

Example

DECLARE @text VARCHAR(100)
SET @text = 'This is a SQL Server tutorial'
SELECT PATINDEX('%SQL%', @text) as Position

Output

Position
---------
9

Explanation

The PATINDEX function returns the starting position of the first occurrence of a pattern in a specified expression, or zero if the pattern is not found. In the provided example, it returns the position of the substring ‘SQL’ in the text ‘This is a SQL Server tutorial’. The function found that ‘SQL’ starts at position 9, so the output is 9.