जगदीश खोलिया: CHARINDEX vs PATINDEX

Thursday, October 25, 2012

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

No comments: