PATINDEX Instruction Result Difference between SQL 7 and SQL 2000.

pts.
Tags:
Development
SQL
I am running the same SQL statement on SQL2000 with different results from SQL 7. What I'm actually trying to do is search a 30 byte character string for a certain pattern. I am looking for the following 9 digit pattern. 1) 1st Digit = any number (0-9) 2) 2nd Digit = any number (0-9) or the letter A,B,C,F,H,M,P,U,or Y. 3) 3rd Digit = any number (0-9) or the letter 4) 4th Digit = any number (0-9) or the letter 5) 5th Digit = any number (0-9) or the letter 6) 6th Digit = any number (0-9) or the letter 7) 7th Digit = any number (0-9) or the letter 8) 8th Digit = Any thing 9) 9th Digit = any number (0-9) or the letter If that pattern is found Ok the resulting field has the correct information. If that pattern is not found a NULL value is passed in the resulting field in SQL 7 which is what we expect for a reult. However, in SQL 2000 it passes back an invalid patterned field as shown below. SQL instruction: select SUBSTRING('QUOTE ISSUED: NFHUM48A1 ', PATINDEX('%[0-9][ABCFHMPUY0-9][0-9][0-9][0-9][0-9][0-9]_[0-9]%','QUOTE ISSUED: NFHUM48A1 '), 9) Resulting field SQL 2000 = 'QUOTE IS ' Resulting field SQL 7 = Null (This is desired result) Can anyone give me a suggestion on how to obtain the same result as was received in SQL 7 or a different method to accomplish the same issue. Thanks Rich

Answer Wiki

Thanks. We'll let you know when a new response is added.

Sorry but I am just a novice at SQL. I probably wouldn’t be much help to you.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following