INF: Explanation of SQL Server PATINDEX Parameters and Output

ID Number: Q69455

1.10 1.11 4.20

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.

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 reference words: Transact-SQL