CHARINDEX and PATINDEX Function in Sql Server

SQL Server String Functions

SQL Server string functions are scalar functions that perform an operation on a string input value and return a string or numeric value.


CHARINDEX and PATINDEX Function

Transact-SQL supports two functions for finding an occurrence of a particular character (or number of characters) within a string: CHARINDEX and PATINDEX. CHARINDEX finds the starting position of a single character or string within a given column (or variable). In addition, if you suspect that the value you are searching for might occur multiple times within a given string you can specify the character number at which you want to start searching. The syntax of the function is:

CHARINDEX(search value, string, starting search location)

For example, you might wish to find a position of an apostrophe inside employee last names. The following query shows how this can be achieved using CHARINDEX function:

CHARINDEX and PATINDEX Function in Sql Server

SELECT  CHARINDEX(', LastName) AS ApostrophePosition,  LastName AS FullLastName   
FROM DimEmployee  WHERE lastname LIKE '%%'

Results:

ApostrophePosition FullLastName
2 D'Hers
2 D'sa
Perhaps a more interesting example is finding an occurrence of a string, as opposed to an occurrence of a character. For example, city names often end with "ville", as in Huntsville or Clarksville. The following query finds the starting position of "ville" within city names:

SELECT  CHARINDEX('ville', city) AS Position,  City    FROM dimGeography  WHERE city LIKE '%ville'

Results:

Position City
5 Daleville
10 Campbellsville
4 Melville
6 Crossville
5 Maryville

The next example finds the occurrence of the value within a variable, starting search at the 20th character:

DECLARE @variable VARCHAR(255)  
SELECT @variable = 'this is a string. this is also a string'  
SELECT  CHARINDEX('string', @variable, 20) AS Position

Results:

Position

34

PATINDEX function is very similar to CHARINDEX - it also finds the position of the first occurrence of a character or multiple characters. The difference is that you have to append % wildcards to PATINDEX; this function searches for a pattern. If you use a % wildcard with CHARINDEX you won't find anything, unless your data contains percent signs. If you're searching for a pattern at the end of the string expression, then you only have to use the % wildcard at the beginning of the pattern to be found, as in PATINDEX ('%pattern_to_find', string). The following query returns the same results as CHARINDEX example:

SELECT  PATINDEX('%ville%', city) AS Position,  City FROM dimGeography  WHERE city LIKE '%ville%'