PATINDEX
Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.
Syntax
PATINDEX ( '%pattern%' , expression )
Arguments
pattern
Is a character expression that contains the sequence to be found. Wildcard characters can be used; however, the % character must come before and follow pattern (except when you search for first or last characters). pattern is an expression of the character string data type category.
expression
Is an expression, typically a column that is searched for the specified pattern. expression is of the character string data type category.
Return types
bigint if expression is of the varchar(max) or nvarchar(max) data types; otherwise an int.
Example
Using wildcard characters with PATINDEX.
SELECT PATINDEX('%en_ure%', 'please ensure the door is locked');