PATINDEX function [String]

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, 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:

    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.

Remarks

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

See also
Standards and compatibility

  • SQL/2003   Vendor extension.

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

The following statement returns 14 which is the first non-alphanumeric character in the string expression. Note that the pattern '%[^a-z0-9]%' can be used instead of '%[^a-zA-Z0-9]%' if the database is case insensitive. 

SELECT PATINDEX( '%[^a-zA-Z0-9]%', 'SQLAnywhere11 has many new features' );

To get the first alphanumeric word in a string, you can use something like the following:

SELECT LEFT( @string, PATINDEX( '%[^a-zA-Z0-9]%', @string ) );
Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0