SQL 2k uses ISNUMERIC(column) to determine if it's numeric or not. SELECT ISNUMERIC(myCol) AS NumericTest FROM Table
I'm not sure about Oracle, though. If Oracle does not have a function, you could probably use a function that parses a numeric value and then compares it to the original value. If they are the same, then you could assume it is numeric. SELECT * FROM Table WHERE PARSEINT(myCol) = myCol (I just made up the PARSEINT() function. But you get the gist of it.) M!ke -----Original Message----- From: Robert Everland III [mailto:[EMAIL PROTECTED] Sent: Thursday, February 09, 2006 1:52 PM To: CF-Talk Subject: SQL Challenge Is there a way in MSSQL and Oracle or either that will allow you to do a select on a varchar field and be able to see if that record can be converted to a number? We have a ton of databases that used varchar to handle numbers and are getting issues now that some records have gotten data other than a number in them. I want to convert them to an integer field but can't do that until we fix the problem records. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231829 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
