July 18, 2014

SQL Server - PATINDEX() function

PATINDEX() function helps to return the starting position of a pattern in a string. It takes two arguments, the pattern to be searched and the expression (string), and is not case sensitive. This will return zero if it doesn't find the pattern.

Syntax:

PATINDEX('%pattern%, expression')

This function supports same as LIKE operator which includes % wildcard for one or more characters and the _ wildcard for any single character.

The string that is enclosed inside % character is the pattern. The pattern can also be used as a pattern using character such as "_", "[]" and [^].

% helps to match one or more characters.
_  helps to match any single character.
[] helps to match any character within the []
[^] helps to match all the character that is not in [^]

It would be more clear how PATINDEX works in the SQL Server looking at the example below:

DECLARE @var VARCHAR(255)= 'PATINDEX() will return zero if it does not find the pattern.'
SELECT
            PATINDEX('%n_t%', @var) AS 'not',
            PATINDEX('%pat%', @var) AS 'PATINDEX',
            PATINDEX('%pat[^i]%', @var) AS 'pattern',
            PATINDEX('%w[a-i]%', @var) AS 'will'


No comments: