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?
 
reb73Commented:
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..
0
 
reb73Commented:
Forward slash '/' is and indicator for an escape character following the forward slash, try without the forward slash in the patindex..
0
 
reb73Commented:
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*+')
0
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.
0
 
Scott PletcherSenior DBAAuthor Commented:
BLESS YOU REB73!!

I overlooked that somehow.  **Thanks sooo much**.  This was driving me crazy :-), since I had the code sooo close to working.
0
 
Scott PletcherSenior DBAAuthor Commented:
Good catch!  Fantastic!!  Thanks.
0
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.