pattern

Is a literal string. 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.

bigint if expression is of the varchar(max) or nvarchar(max) data types, otherwise int.

If either pattern or expression is NULL, PATINDEX returns NULL when the database compatibility level is 70. If the database compatibility level is 65 or earlier, PATINDEX returns NULL only when both pattern and expression are NULL.

PATINDEX performs comparisons based on the collation of the input. To perform a comparison in a specified collation, you can use COLLATE to apply an explicit collation to the input.

A. Using a pattern with PATINDEX

USE AdventureWorks;
GO
SELECT PATINDEX('%ensure%',DocumentSummary)
FROM Production.Document
WHERE DocumentID = 3;
GO

----------- 
64          

(1 row(s) affected)

B. Using wildcard characters with PATINDEX

USE AdventureWorks;
GO
SELECT PATINDEX('%en_ure%', DocumentSummary)
FROM Production.Document
WHERE DocumentID = 3;
GO

------------
64

(1 row(s) affected)

C. Using COLLATE with PATINDEX

USE tempdb;
GO
SELECT PATINDEX ( '%ein%', 'Das ist ein Test'  COLLATE Latin1_General_BIN) ;
GO

Reference

Help and Information