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

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?
LVL 71
Scott PletcherSenior DBAAsked:
Who is Participating?
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..
Forward slash '/' is and indicator for an escape character following the forward slash, try without the forward slash in the patindex..
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*+')
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Anthony PerkinsCommented:
Good one.
Scott PletcherSenior DBAAuthor Commented:

I overlooked that somehow.  **Thanks sooo much**.  This was driving me crazy :-), since I had the code sooo close to working.
Scott PletcherSenior DBAAuthor Commented:
Good catch!  Fantastic!!  Thanks.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.