April 9, 2009 at 1:35 pm
This is a small example, but it seems to fail (but only sometimes) on large spreadsheets.
The format of the the file might be in a excel spreadsheet that looks like this (I'm using pipes to delimit cells):
Title | Value
Average Number of kids in class|32
Total Number of teachers in the school|55
Principals name|Andrew Moore
The format after being loaded into the database by my automated system is like this (I'm using pipes to delimit columns). Note that the Col is an int, Row is an int, and Value is varchar(255).
Col | Row | Value
-------------------
1 | 1 | Average Number of kids in class
1 | 2 | 32
2 | 1 | Total Number of teachers in the school
2 | 2 | 55
3 | 1 | Principals name
3 | 2 | Andrew Moore
The problem is that sometimes the following query works and sometimes it tells me "Conversion failed when converting numeric from character string."
SELECT cast(Value as decimal(5,2))
FROM FundData
WHERE Value is not null
and ISNUMERIC(Value) = 1
I do have multiple databases that this runs on and the same query will fail in some cases. Does anyone know what might cause something like this?
April 9, 2009 at 1:38 pm
Stupidly enough, you will get that error because of the Cast, even with the Where clause limiting it. I've seen that dozens of times. You shouldn't, but you sometimes do.
What I do is insert the raw data into a temp table, using the IsNumeric piece in the Where clause, then do the Cast on the temp table.
Seems to work better.
Try that, let me know if that doesn't do it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 9, 2009 at 2:26 pm
That works in many cases, but in some of the more complex queries, this may be pretty difficult. Is there a way to "encourage" SQL Server to run some clauses first or enforce priorities? In the more complicated queries, I've tried to do virtual tables SELECT * FROM (SELECT * FROM ...) AS Temp, however SQL Server seems to work around that too.
April 9, 2009 at 2:46 pm
The only way to force the sequence is to make them separate queries. That doesn't mean a CTE or derived table, it means a table variable or temp table. For various reasons, I recommend temp tables for that.
You'll probably also find it's faster when you break the job down that way.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 9, 2009 at 2:49 pm
I've never seen this error, but I won't doubt. Just an idea:
Is it possible to add an column IsNumber to the table? If yes you can UPDATE once after loading the data with "SET IsNumber = ISNUMERIC(Value)". After done you can use this column instead of ISNUMERIC.
Greets
Flo
April 9, 2009 at 3:47 pm
The other potential problem being that there are cases where ISNUMERIC returns true, but the value cannot be cast to decimal. ISNUMERIC means that the value in question can be cast to one of the numeric data types. Which one is a completely different issue.
SELECT ISNUMERIC('15d1') -- retrns 1
SELECT CAST ('15d1' AS decimal(5,2))
-- Msg 8114, Level 16, State 5, Line 2
-- Error converting data type varchar to numeric.
SELECT ISNUMERIC('15e1') -- returns 1
SELECT CAST ('15e1' AS decimal(5,2))
-- Msg 8114, Level 16, State 5, Line 2
-- Error converting data type varchar to numeric.
SELECT ISNUMERIC('1,00') -- returns 1
SELECT CAST ('1,00' AS decimal(5,2))
-- Msg 8114, Level 16, State 5, Line 1
-- Error converting data type varchar to numeric.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 9, 2009 at 3:55 pm
GilaMonster (4/9/2009)
The other potential problem being that there are cases where ISNUMERIC returns true, but the value cannot be cast to decimal. ISNUMERIC means that the value in question can be cast to one of the numeric data types. Which one is a completely different issue.
SELECT ISNUMERIC('15d1') -- retrns 1SELECT CAST ('15d1' AS decimal(5,2))
-- Msg 8114, Level 16, State 5, Line 2
-- Error converting data type varchar to numeric.
SELECT ISNUMERIC('15e1') -- returns 1
SELECT CAST ('15e1' AS decimal(5,2))
-- Msg 8114, Level 16, State 5, Line 2
-- Error converting data type varchar to numeric.
SELECT ISNUMERIC('1,00') -- returns 1
SELECT CAST ('1,00' AS decimal(5,2))
-- Msg 8114, Level 16, State 5, Line 1
-- Error converting data type varchar to numeric.
In some countries (like here 😉 ) the "." and "," are switched. Anyway since the value cannot be converted to a number it also shouldn't be numeric...
Greets
Flo
April 9, 2009 at 4:17 pm
Thanks guys, I'm glad that there's no obvious solution to this, as I was feeling down about it. Regardless, it is really good to know that the ISNUMERIC is not 100% guarantee that the number will be ok too. I'll have to look into this in my development as well.
April 10, 2009 at 1:00 am
Florian Reischl (4/9/2009)
Anyway since the value cannot be converted to a number it also shouldn't be numeric...
Oh, but they can.
'15d1' and '15e1' will cast successfully to float. '10,0' will cast successfully to money.
Yes, it's weird. Yes, it's irritating.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 10, 2009 at 3:09 am
GilaMonster (4/10/2009)
Florian Reischl (4/9/2009)
Anyway since the value cannot be converted to a number it also shouldn't be numeric...Oh, but they can.
'15d1' and '15e1' will cast successfully to float. '10,0' will cast successfully to money.
Yes, it's weird. Yes, it's irritating.
Hey Gail
I knew that 15e1 would be a valid number but "15d1" and 12,0" where new for me. Thanks for the explanation!
Flo
April 10, 2009 at 7:05 am
right. the problem is that what we really need in SQL is a function like this: isCastable(expression, datatype) So that we can do proper pre-testing.
Actually I think that the ANSI/ISO committee has been talking about this (long past due IMHO).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 10, 2009 at 7:25 am
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=354766
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 10, 2009 at 8:19 am
Heh. I should have known that Erland would have already requested this. Between him and Itzik, I think that they must have almost 100 of these.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 10, 2009 at 8:30 am
Erland, Itzik, Steve Kass and Aaron. Between the 4 of them they probably opened more than 50% of the connect suggestions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 10, 2009 at 8:40 am
RBarryYoung (4/10/2009)
Heh. I should have known that Erland would have already requested this. Between him and Itzik, I think that they must have almost 100 of these.
Not to change the subject, Barry, but have you gone completely to the Dark Side? It could just be my connection here at work, but your avatar is completely black.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply