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:
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%'
Tags:
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:
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%'