Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

PATINDEX function [String]

Returns an integer representing the starting position of the first occurrence of a pattern in a string.

PATINDEX( '%pattern%', string_expression )

pattern    The pattern to be searched for. If the leading percent wildcard is omitted, the PATINDEX function returns one (1) if the pattern occurs at the beginning of the string, and zero if not.

The pattern uses the same wildcards as the LIKE comparison. These are as follows:

string-expression    The string to be searched for the pattern.

The PATINDEX function returns the starting position of the first occurrence of the pattern. If the pattern is not found, it returns zero (0).

• LIKE search condition
• LOCATE function [String]
• String functions

• SQL/2003    Vendor extension.

The following statement returns the value 2.

SELECT PATINDEX( '%hoco%', 'chocolate' );

The following statement returns the value 11.

SELECT PATINDEX( '%4_5_', '0a1A 2a3A 4a5A' );