Friday, September 5, 2014

SQL Functions – PATINDEX()

CHARINDEX() can only be used to search a literal string in the specified expression. In other words you cannot use wildcards. PATINDEX() provides this capability. It takes two arguments, the pattern to be searched and the expression.
– © 2011 – Vishal (

DECLARE @string VARCHAR(128)
SET @string =     'PATINDEX searches the string for the ' +
'first occurrence of a specified ' +
SELECT      PATINDEX('%s_t%', @string) AS 's t',
            PATINDEX('%pat%', @string) AS 'PATINDEX',
            PATINDEX('%pat[^i]%', @string) AS 'pattern',
            PATINDEX('%f[a-i]r%', @string) AS 'first'
Result Set:
s t         PATINDEX    pattern     first
———– ———– ———– ———–
17          1           87          38

(1 row(s) affected)
First column uses the _ Wildcard, which matches any one character, which is between 's' and 't'.
The second column uses % Wildcard, which searches for 0 or more characters. It is same as using: CHARINDEX('pat', @string).
Third column uses a ^ Wildcard, which matches the characters not matching the specified set. Hence, ignoring the 'PATINDEX'.
Last column matches the characters specified in the range using [ ] Wildcard. Since the set specifies that only 'a' to 'i', it ignores the 'for'.
Pattern matching is based on the data collation. We can use COLLATE to enforce a case-sensitive search:
SELECT      PATINDEX('%[S]tring%', @string) AS 'string',
            PATINDEX('%[S]tring%', @string COLLATE Latin1_General_CS_AI)
            AS 'String'
Result Set:
string      String
———– ———–
23          80

(1 row(s) affected)
Hope This Helps!

No comments:

Post a Comment