PATINDEX( '%pattern%', string-expression )

INT

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

• LOCATE function [String]
• UltraLite string functions

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]%', 'SQLAnywhere12 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 ) );