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