PATINDEX function [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 it does 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.

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

SQL/2008 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' );

The following statement returns 14 which is the first non-alphanumeric character in the string expression. 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 everything up to and including the first non-alphanumeric character in a string, you can use something like the following:

SELECT LEFT( @string, PATINDEX( '%[^a-zA-Z0-9]%', @string ) );

The following statements create a table, myTable, and populate it with various strings containing alphanumeric characters, spaces (blanks), and non-alphanumeric characters. Then, the SELECT statement and subsequent results show how you can use PATINDEX to find the starting position of spaces and non-alphanumeric characters in the strings:

CREATE TABLE myTable( col1 LONG VARCHAR );

INSERT INTO myTable (col1) VALUES( 'the quick brown fox jumped over the lazy dog' ), 
( 'the quick brown fox $$$$ jumped over the lazy dog' ),
( 'the quick brown fox 0999 jumped over the lazy dog' ),
( 'the quick brown fox ** jumped over the lazy dog' ),
( 'thequickbrownfoxjumpedoverthelazydog' ),
( 'thequickbrownfoxjum999pedoverthelazydog' ),
( 'thequick$$$$brownfox' ),
( 'the quick brown fox$$ jumped over the lazy dog' );

SELECT col1,
   //position of first non-alphanumeric character or space:
   PATINDEX( '%[^a-z0-9]%', col1) AS blank_posn, 
   //position of first non-alphanumeric char that isn't a space:
   PATINDEX( '%[^ a-z0-9]%', col1) AS non_alpha_char,
   //everything up to and including first non-alphanumeric char that isn't a space:
   LEFT ( col1, PATINDEX( '%[^ a-zA-Z0-9]%', col1) ) AS left_str,
   //first non-alphanumeric char that isn't a space, and everything to the right:
   SUBSTRING ( col1, PATINDEX( '%[^ a-zA-Z0-9]%', col1) ) AS sub_str
FROM myTable;

col1	blank_posn	non_alpha_char	left_str	sub_str
the quick brown fox jumped over the lazy dog	4	0		the quick brown fox jumped over the lazy dog
the quick brown fox $$$$ jumped over the lazy dog	4	21	the quick brown fox $	$$$$ jumped over the lazy dog
the quick brown fox 0999 jumped over the lazy dog	4	0		the quick brown fox 0999 jumped over the lazy dog
the quick brown fox ** jumped over the lazy dog	4	21	the quick brown fox *	** jumped over the lazy dog
thequickbrownfoxjumpedoverthelazydog	0	0		thequickbrownfoxjumpedoverthelazydog
thequickbrownfoxjum999pedoverthelazydog	0	0		thequickbrownfoxjum999pedoverthelazydog
thequick$$$$brownfox	9	9	thequick$	$$$$brownfox
the quick brown fox$$ jumped over the lazy dog	4	20	the quick brown fox$	$$ jumped over the lazy dog

PATINDEX function [String]

Syntax

Parameters

Returns

Remarks

See also

Standards and compatibility

Example