CHARINDEX vs PATINDEX

The CHARINDEX and PATINDEX functions return the starting position of a pattern you specify.

Both functions take 2 parameters.For CHARINDEX, the pattern cannot include wildcard characters. 

With PATINDEX, you must include percent signs before and after the pattern, unless you are looking for the pattern as the first (omit the first %) or last (omit the last %) characters in a column.  The second argument is a character expression  in which searches for the specified pattern.

Retrive File Extension from given File Name  using charindex and patindex :

Example of CHARINDEX:

DECLARE @FileName VARCHAR(200)
SET @FileName='jagdish.doc'
SELECT SUBSTRING(@FileName,charindex('.', @FileName)+1 ,LEN(@FileName)) AS Extention
GO

Examples of PATINDEX:

DECLARE @FileName VARCHAR(200)
SET @FileName='jagdish.doc'
SELECT SUBSTRING(@FileName,patindex('%.%', @FileName)+1 ,LEN(@FileName)) AS Extention