SQL Server PATINDEX() Function

 The PATINDEX() function in SQL Server is used to return the starting position of the first occurrence of a pattern in a string. The pattern can be a literal string or a wildcard pattern, and the function returns the position of the first character of the matched pattern. If no match is found, it returns 0.

Syntax:

PATINDEX ( '%pattern%' , expression )

• '%pattern%': The pattern you are looking for, with % representing any sequence of characters (used as wildcards).
• expression: The string expression to search within.

Example Usage:

1. Basic Pattern Search

   SELECT PATINDEX('%apple%', 'This is an apple pie.') AS Position;
   

   This will return 12, as the word "apple" starts at position 12 in the string "This is an apple pie."

2. Wildcard Search

   SELECT PATINDEX('%_n%', 'banana') AS Position;
   

   This will return 2, as the substring "an" starts at position 2 in "banana". The _ wildcard represents any single character.

3. Using PATINDEX in a WHERE Clause

   SELECT ProductName
   FROM Products
   WHERE PATINDEX('%apple%', ProductName) > 0;
   

   This will return all products whose names contain the word "apple".

Notes:

• PATINDEX() is case-insensitive by default, but it depends on the collation setting of the column or expression.
• You can use PATINDEX() with various wildcards:
  • % matches any number of characters (including zero).
  • _ matches a single character.