Heh, I think we'll all do it again at some point in the future. :)
Anyway, just to give another option to the OP, two thirds of the way down the following page, there is a bit on creating an is_number function for Oracle. http://www.oracle.com/technology/oramag/oracle/04-jul/o44asktom.html HTH On Fri, Feb 22, 2008 at 7:39 PM, Ray Thompson <[EMAIL PROTECTED]> wrote: > Got it. I shot from the hip without all the information needed. I will > probably do it again at some point in the future. > > Ray Thompson > Tau Beta Pi (www.tbp.org) > The Engineering Honor Society > 865-546-4578 > > -----Original Message----- > From: Peter Boughton [mailto:[EMAIL PROTECTED] > Sent: Friday, February 22, 2008 2:36 PM > To: SQL > Subject: Re: find non-numbers > > >> Where IsNumeric(column_name) = 1 > > That's an MS SQL Server function, and is apparently not a reliable check > even then. > > I supplied an Oracle RegEx above which should work (only for integers > though > - needs to be made smarter if decimals are involved). > > > On Fri, Feb 22, 2008 at 7:26 PM, Ray Thompson <[EMAIL PROTECTED]> wrote: > > > You indicated the column was only two characters if my memory is > correct. > > This was just an example. You could extend that to two columns easily by > > using OR. 'ASD9' would not qualify as a number in this case as only the > > single most left character was used. > > > > Where ( > > not Left(LTrim(RTrim(column_name)),1) between '0' and '9' > > or > > not Right(LTrim(RTrim(column_name)),1) between '0' and '9' > > ) > > > > I suppose you could leave off the trim functions quite easily for your > two > > character column. > > > > You can also use > > > > Where IsNumeric(column_name) = 1 > > > > The problem with that, for multiple character columns is that '1E5' will > > evaluate as a valid number. It is a valid representation of a floating > > point > > number that can be converted by SQL so SQL considers it a valid number. > > > > Ray Thompson > > Tau Beta Pi (www.tbp.org) > > The Engineering Honor Society > > 865-546-4578 > > > > -----Original Message----- > > From: Ian Skinner [mailto:[EMAIL PROTECTED] > > Sent: Friday, February 22, 2008 2:14 PM > > To: SQL > > Subject: Re: find non-numbers > > > > Ray Thompson wrote: > > > Where not Left(LTrim(RTrim(column_name)),1) between '0' and '9' > > > > > > Ray Thompson > > > Tau Beta Pi (www.tbp.org) > > > The Engineering Honor Society > > > 865-546-4578 > > Wouldn't that say something like 'ASD9' is a 'number' just because the > > last character is a digit? > > > > I don't know what kind of bad data I am looking at here. I haven't > > found a way to pull just an example of a bad record out and doing a > > brief search of the beginning of the 4 million odd records, I did not > > see any examples of bad data. > > > > I just know when I do a query that scans the entire table expecting > > these values to be number I get an Oracle error that says it can't > > because something is not a number. > > > > > > > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:3043 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
