|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
IsNumeric functionI have an excel file that I am using to populate a table in SQL 2000 using
OpenRowSet. If the excel cell contains more than 255 characters the IsNumeric function errors. I can populate the table in other columns with > 255 characters when I do not use the IsNumeric function. If the cell contains numeric values, I trim it and cast it as a string, if it is not i cast it as varchar. Is there a way to get around this or Should I not use the IsNumeric function? Take a look at dbo.IsReallyNumeric()... it would be trivial to add a length
verifier there, so that you ignore all values that are greater than x characters, regardless of whether all characters are numeric are not... http://www.aspfaq.com/2390 Show quote "orourksj" <orour***@discussions.microsoft.com> wrote in message news:00123751-4CB6-496A-B490-1C2612C2EE22@microsoft.com... >I have an excel file that I am using to populate a table in SQL 2000 using > OpenRowSet. If the excel cell contains more than 255 characters the > IsNumeric > function errors. I can populate the table in other columns with > 255 > characters when I do not use the IsNumeric function. If the cell contains > numeric values, I trim it and cast it as a string, if it is not i cast it > as > varchar. Is there a way to get around this or Should I not use the > IsNumeric > function? There are issues with the IsNumeric function that you will want to consider.
A number prefixed by $, +, and even Tab will return a value of 1. For example: isnumeric('$0') = 1 but ... convert(int,'$0') = Syntax error converting the varchar value '$0' to a column of data type int. convert(money,'$0') = Implicit conversion from data type money to nvarchar is not allowed. Use the CONVERT function to run this query. http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/8d02d3cf475a7dd3/de1eae3901eb082e Show quote "orourksj" <orour***@discussions.microsoft.com> wrote in message news:00123751-4CB6-496A-B490-1C2612C2EE22@microsoft.com... >I have an excel file that I am using to populate a table in SQL 2000 using > OpenRowSet. If the excel cell contains more than 255 characters the > IsNumeric > function errors. I can populate the table in other columns with > 255 > characters when I do not use the IsNumeric function. If the cell contains > numeric values, I trim it and cast it as a string, if it is not i cast it > as > varchar. Is there a way to get around this or Should I not use the > IsNumeric > function? Thank you for your prompt response. I believe that I have figured it out by
removing the IsNumeric Function. I am uncertain why the SP was using it there anyway. Thank you for your assistance. Show quote "orourksj" wrote: > I have an excel file that I am using to populate a table in SQL 2000 using > OpenRowSet. If the excel cell contains more than 255 characters the IsNumeric > function errors. I can populate the table in other columns with > 255 > characters when I do not use the IsNumeric function. If the cell contains > numeric values, I trim it and cast it as a string, if it is not i cast it as > varchar. Is there a way to get around this or Should I not use the IsNumeric > function?  |
|||||||||||||||||||||||
Other interesting topics