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.
PATINDEX ( '%pattern%' , expression )
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.
Is an expression, typically a column that is searched for the specified pattern. expression is of the character string data type category.
bigint if expression is of the varchar(max) or nvarchar(max) data types; otherwise an int.
Using wildcard characters with PATINDEX.
SELECT PATINDEX('%en_ure%', 'please ensure the door is locked');