PATINDEX

PATINDEX is a SQL Server function that returns the starting position of a specified pattern in a string. It is often used in text manipulation to identify the location of a sub-string within a larger string.

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.

For in-depth explanations and examples SQL keywords where you write your SQL, install our extension.