I think the -2222 is matching the first expression, since the '-' is not a number, which accounts for the false negative. I'm starting to think that you may not be able to take this approach using SQL expressions - the expression syntax for pattern matching is simply too simplistic and not suited to this type of operation. That said, though, which database are you using? Some databases have extended pattern matching syntax.
Otherwise, if you really want to do it in SQL, you may be stuck with taking the approach outlined by Misi and look for positive matches rather than negative matches. The catch there is you have to specify all possible number lengths. Lyle -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Mark Milke Sent: Wednesday, February 03, 2010 1:46 AM To: [email protected] Subject: Re: How to allow a specific character as first character? Lyle, >That said, if your approach is to match the opposite and negate it, then that >might work. >If so, then you might be able to do it like this: > 'Field1' LIKE "%[^0-9]%" OR 'Field1' LIKE "[^-]%[^0-9]%" Yes, I want to match the opposite to show an error message. The suggested qualification isn't working however. The qualification 'Field1' LIKE "%[^0-9]%" OR 'Field1' LIKE "[^-]% [^0-9]%" works just fine to prevent users from entering alpha characters. Unfortunately it also prevents strings like -2222 for some reason... The qualification 'Field 1' LIKE "[^-]%" AND 'Field 1' LIKE "% [^0-9]%" is very close to what I'm looking for, but it allows input like -m000 for some reason. @Misi I'm don't want to use patterns, because they generate a very user unfriendly error messages. I'm trying to avoid specyfing all possiblities. If the wildcards work as documented, then it should be possible to tackle the problem without it. Mark _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:[email protected] ARSlist: "Where the Answers Are" NOTICE: This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:[email protected] ARSlist: "Where the Answers Are"
