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:3042 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
