Contents Index OPENXML function [String] PI function [Numeric]

ASA SQL Reference
  SQL Functions
    Alphabetical list of functions

PATINDEX function [String]


Function 

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

Syntax 

PATINDEX ( '%pattern%'string_expression )

Parameters 

pattern    The pattern to be searched for. If the leading percent wildcard is omitted, PATINDEX 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:

Wildcard Matches
_ (underscore) Any one character
% (percent) Any string of zero or more characters
[] Any single character in the specified range or set
[^] Any single character not in the specified range or set

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

Usage 

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

Standards and compatibility 
See also 

LIKE conditions

LOCATE function [String]

Example 

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')

Contents Index OPENXML function [String] PI function [Numeric]