You didn't specify which database engine you're using? For PostgreSQL: WHERE field_name ~ '^[0-9]+$'
For Oracle: WHERE REGEXP_LIKE(field_name, '^[0-9]+$'); For MySQL: WHERE field_name REGEXP '^[0-9]+$' For MS SQL Server: WHERE (NOT field_name LIKE '%[^0-9]%') AND field_name <> '' (And if you're not using one of those, lookup how to do regular expressions and adapt as appropriate.) On Fri, Feb 22, 2008 at 6:49 PM, Ian Skinner <[EMAIL PROTECTED]> wrote: > I have a table with a few million records. Somewhere buried in there is > at least one, probably many more data fields that should be a number but > are not. The actual field type is varchar2(), but it is supposed to > contain numeric data. This is not going to change, this data has > existed as it is since 1974 and nobody is going to change it at this time. > > How can I find and|or filter out these non-number numbers so that I can > join this data to another field that is a proper number(5) field? > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:3039 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
