Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 985
  • Last Modified:

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?
0
Scott Pletcher
Asked:
Scott Pletcher
  • 3
  • 2
1 Solution
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now