Why does PATINDEX() return incorrect values at various times?

Posted on 2009-02-17
Last Modified: 2012-06-21
My SQL Server returns 4 as the result of:

SELECT PATINDEX('%[*+-/]%', '|11.0000000000|123-|123456|1234-|5*+')

What am I doing wrong here?  Isn't the first * or + or - or / until much later in the string than position 4??

Other times PATINDEX() will return 0 even when one of the chars is clearly present in the string.

I get the same result from SQL versions 8.00.2055 and 9.00.4035.00 (X64).

What am I specifying wrong?

Or does anyone know of a bug (and fix) for PATINDEX() in SQL Server?
Question by:ScottPletcher
    LVL 25

    Expert Comment

    Forward slash '/' is and indicator for an escape character following the forward slash, try without the forward slash in the patindex..
    LVL 25

    Expert Comment

    Try escaping the forward slash by doubling it and placing it immediately after the '[' character like -

    SELECT PATINDEX('%[//*+-]%', '|11.00000000/00|123-|123456|1234-|5*+')
    LVL 25

    Accepted Solution

    Just figured it out, you had the hyphen '-' between '+' and '/', which fooled the parser into thinking +-/ was a range between + and /..

    It was actually the hyphen which was causing the issue, leaving it at the end is ensuring that it is used as a literal search character..
    LVL 75

    Expert Comment

    by:Anthony Perkins
    Good one.
    LVL 68

    Author Comment


    I overlooked that somehow.  **Thanks sooo much**.  This was driving me crazy :-), since I had the code sooo close to working.
    LVL 68

    Author Closing Comment

    Good catch!  Fantastic!!  Thanks.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now