INF: Explanation of SQL Server PATINDEX Parameters and Output

ID: Q69455

The information in this article applies to:

Microsoft SQL Server for OS/2, version 4.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

Instead, you should use one of the following queries:


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

   -or-

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

This second SELECT returns a value of 3.

Additional query words: Transact-SQL


Keywords          : kbusage SSrvTran_SQL 
Version           : 4.2
Platform          : OS/2 
Issue type        :

Last Reviewed: March 10, 1999