Posts Tagged ‘string search’

SQL Functions – PATINDEX()

05.07.2011 2 comments

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[#k8SjZc9Dxki]%', @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 #k8SjZc9Dxk 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!