Home > SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2 > SQL Functions – PATINDEX()

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 (http://SqlAndMe.com)

 

DECLARE @string VARCHAR(128)

SET @string =     'PATINDEX searches the string for the ' +

'first occurrence of a specified ' +

'character/String/pattern'

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!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Advertisements
  1. Suha
    November 3, 2012 at 3:38 am

    How do we use PATINDEX if we are looking for the ‘[‘ characater?

  2. iftikhar
    March 13, 2013 at 6:49 pm

    Hi Suha,
    You need to use CHARINDEX() function for that purpose.

    It can be used in conjunction with patindex() in a query. The above example can be modifed in this way

    DECLARE @string VARCHAR(128)

    SET @string = ‘PATINDEX searches the string for the ‘ +

    ‘first occurrence of a [specified] ‘ +

    ‘character/String/pattern’+ ‘New[Addition]’

    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’,

    CHARINDEX(‘[‘,@string) AS ‘ [ ‘,

    CHARINDEX(‘[‘,REVERSE(@string)) AS ‘ [ ‘

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: