INF: Explanation of SQL Server PATINDEX Parameters and Output

  - Microsoft SQL Server version 4.2 for OS/2

SUMMARY

The PATINDEX text/image function is defined as follows:

   Function    Parameters               Result
   --------    ----------               ------

   PATINDEX    ("pattern",column_name)  The starting position of
                                        the first occurrence of
                                        pattern in the specified
                                        column. You can use
                                        wildcard characters in
                                        pattern.

MORE INFORMATION

This means that PATINDEX will return the starting position of the string if it finds a match. If, however, a match is not found, the result is 0 (zero). The string pattern follows the standard rules regarding wildcards. The following SELECT PATINDEX returns 0 because there is no % wildcard to direct SQL Server to look for the pattern starting after the first byte:

   create table tab(col1 text)
   go
   insert tab values("testing")
   go
   select patindex("sting",col1) from tab
   go

   select patindex("%sting",col1) from tab

   -or-

   select patindex("%sting%",col1) from tab