Microsoft KB Archive/69455

-

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    (&quot;pattern&quot;,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(&quot;testing&quot;) go  select patindex(&quot;sting&quot;,col1) from tab go

Instead, you should use one of the following queries:

select patindex(&quot;%sting&quot;,col1) from tab

-or-

select patindex(&quot;%sting%&quot;,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