I am not sure why I am getting error when converting a varchar value to float. when I check with isnumeric function the returned value is 1 but when I use convert function to convert it to a float then I get an error.
Error converting data type varchar to float.
Code I am trying to run :-
begin
declare @.value varchar(20)
set @.value= '0,0006876'
select case when isnumeric(@.value) = 1 then convert(float, @.value) else '' end
end
Thanks in advance for your help.
Neeraj
There are known issues relating to why sometimes isnumeric reports true, when in actuality is 'should' be false.
See this article for more information:
isnumeric -What is wrong?
http://www.aspfaq.com/show.asp?id=2390
Also, if you were to search this forum for ISNUMERIC, you would find several threads that go into a great bit of detail about the issues and how the article above provides some ways to handle the issues.
|||
The number is, in fact, numeric. There's nothing wrong with the function in this instance you just can't force the conversion.
View http://doc.ddart.net/mssql/sql70/ca-co_1.htm for more information.
Adamus
|||I agree with Arnie; Give a look at the isReallyNumeric function at the web site he referenced.|||Folks, let me explain something:
Yes there are some strange oddities with the function as is there are with all functions. BUT and I repeat BUT this does not mean our first instinct should be to direct someone to a site that says, "There's a problem with the function" especially when the issue is completely irrelevant or, at the very least, has been tested for validity.
This is my biggest problem with these forums. A regular in these forums stumbles upon an article and all of the sudden, everything in the article remotely related is defaulted to the newly found article.
Folks, this is not helping anyone. This is not an endeavor toward resolution. They are not asking, "Please someone, PLEASE, I'm begging you... direct me to an article you've just read. Please post a hyperlink that you have saved in your favorites. I'm not looking for a real answer. I just want to read something."
Example: This post. Arnie as well as his new secondary has not tested the number to ensure it truly is numeric which IT IS, so they read Arnie's new article and decided that this remote coincidence is somehow a scapegoat instead of fixing the real problem;The implicit illegal conversion. BTW: This approach to critical thinking and problem solving is more popularly known as Consulting.
This number is truly numeric. Try it in any language. (try it with regex) It is not language specific.
Please stop posting generalized articles that are not written by M.S. themself and are precise and relevant.
Adamus
|||
Kent Waldrop Jn07 wrote:
I agree with Arnie; Give a look at the isReallyNumeric function at the web site he referenced.
Kent, the only thing you and Arnie agree on is that neither of you have tested the number.
Please read my follow up posting and begin to exercise a troubleshooting algorithm that isn't shot from the hip.
Adamus
|||
This number is truly numeric. Try it in any language. (try it with regex) It is not language specific.
Adamus
From the OP:
declare @.value varchar(20)
set @.value= '0,0006876'
If it is truly 'numeric', then it 'should' convert to a numeric datatype.
select case
when isnumeric(@.value) = 1 then convert(decimal(18,8), @.value)
else 0
end
Server: Msg 8114, Level 16, State 5, Line 3
Error converting data type varchar to numeric.
Very Odd, it passes the ISNUMERIC() function, but then fails to convert to a numeric datatype. Maybe, just perhaps, the ISNUMERIC() function is providing a 'false' positive.
As I recall, the latin based decimal indicator is a period -not a comma. Perhaps there is an issue with the culture/regional settings concerning the decimal/comma indicator between the client application and the server settings.
|||I am also stand behind with Arnie & agree with him.
If the ISNUMERIC returns TRUE then CONVERT function should finish the command. It seams there is a different logic used on these functions on single instance of the Data Base.
PS: I remember French use , as decimal indicator
|||OK, so I wake up at 3:30AM from a bizarre dream about discontinuous functions and I realize that I have gotten this wrong. I had hoped that I could correct / straighte up before I added extra trouble, but no. Adam, all I can tell you is that from time to time we need to pick each other up because none of us has all the answers. It's funny sometimes you can look right at something understand what you are seeing and still get it wrong. Such was the case here. I saw that this was a decimal, knew even a float representation had to be acceptable, knew the problems of the "IsReallyNumeric" function and still failed to connect the dots. I bricked it. Sheesh.
I agree with you about me and the testing issue. I normally try to test and provide results so that I have self-reviewed. It is not as easy from home and so sometimes I miss. Shooting from the hip? Ooooooh, bulls eye. Ouch. All I did was make matters worse.
I do appreciate you picking me up on this -- I definitely feel like I strayed.
Arnie: I am sorry. It appears to me that you were just recommending that the person gve a look to the article just for the sake of understanding the potential problems and I then incorrectly went to far by adding an incorrect comment about the isReallyNumeric function. Sorry about that. Adam, please don't not convict Arnie of this because of something he properly said that I subsequently over-applied incorrectly.
I wish I could tell you both differently; but all I can tell you is I am learning -- that is why I came here. From time to time I am going to get stuff wrong -- unfortunately, you can count on it. From time to time, I am going to fall and I am going to need for somebody to pick me up again. But PLEASE when I am down, pick me up, don't kick me.
|||Kent
Adamus Turner wrote:
This number is truly numeric. Try it in any language. (try it with regex) It is not language specific.
Adamus
From the OP:
declare @.value varchar(20)
set @.value= '0,0006876'
If it is truly 'numeric', then it 'should' convert to a numeric datatype.
select case
when isnumeric(@.value) = 1 then convert(decimal(18,8), @.value)
else 0
end
Server: Msg 8114, Level 16, State 5, Line 3
Error converting data type varchar to numeric.
Very Odd, it passes the ISNUMERIC() function, but then fails to convert to a numeric datatype. Maybe, just perhaps, the ISNUMERIC() function is providing a 'false' positive.
As I recall, the latin based decimal indicator is a period -not a comma. Perhaps there is an issue with the culture/regional settings concerning the decimal/comma indicator between the client application and the server settings.
Arnie,
That was my point the entire time. The problem isn't the isnumeric funtion. It's in the conversion.
Adamus
|||
Very Odd, it passes the ISNUMERIC() function, but then fails to convert to a numeric datatype. Maybe, just perhaps, the ISNUMERIC() function is providing a 'false' positive.
Let me shine a spotlight on this:
declare @.value varchar(20)
set @.value='0,0006876'
set @.value =convert(int, @.value)
Syntax error converting the varchar value '0,0006876' to a column of data type int.
and again...
declare @.value varchar(20)
set @.value= '0,0006876'
set @.value = convert(float, @.value)
Error converting data type varchar to float.
Do I need to continue?
Adamus
|||Neeraj:
Do you expect your input to commonly contain punctuating commas such as the string you stated: '0,0006876'. If that is the case, you might just use a replace function and eliminate the comma. Also, if your comma is inteded as an alternative to the period character, you might replace the comma with a period. Otherwise, this looks like yet another case in which the isNumeric function does not reject a string. Does Steve have something that works for float / decimal?
Code Snippet
declare @.value varchar(20)
set @.value= '0,0006876'
-- If the comma is intended as punctuation (not likely, but):
select case when isnumeric(replace(@.value,',','')) = 1 then convert(float, replace(@.value,',','')) else '' end
/*
--
6876.0
*/
-- If the comma is a substitute for a period:
set @.value= '0,0006876'
select case when isnumeric(replace(@.value,',','.')) = 1 then convert(float, replace(@.value,',','.')) else '' end
/*
--
6.8760000000000002E-4
*/
I do not have a solution for this issue. You might be able to get by until a complete closed solution is found with something like this:
Code Snippet
select case when isnumeric(@.value) = 1
and replace(@.value, ',','') = @.value
then convert(float, @.value) else 0.0 end
We usually are not this bad at answering questions. Once in a long while heated dissent might occur, but it is usually not as big a distraction as this has been. As I said in an earlier post, I came here to learn. For me this has been great place to do so. I feel like Uma, Louis and Arnie have contributed to help me a great deal. Your question has been another step in which I get to learn. Thank you for contributing and I hope you return and continue to contribute.
|||Kent
If it is truly 'numeric', then it 'should' convert to a numeric datatype.
Then why does this have the same problem?
declare @.value varchar(20)
set @.value='6,876'
set @.value =convert(int, @.value)
Syntax error converting the varchar value '6,876' to a column of data type int.
...or as the OP had originally posted:
begin
declare @.value varchar(20)
set @.value='6,876'
selectcasewhenisnumeric(@.value)= 1 thenconvert(float, @.value)else''end
end
Error converting data type varchar to float.
Do you all still believe that 6,876 is not numeric and the isnumeric is returning a false positive?
...or is the comma causing the problem?
Adamus
|||Precisely!
Neeraj:
Do you expect your input to commonly contain punctuating commas such as the string you stated: '0,0006876'. If that is the case, you might just use a replace function and eliminate the comma. Also, if your comma is inteded as an alternative to the period character, you might replace the comma with a period. Otherwise, this looks like a correct rejection by SQL Server.
Adamus
|||I guess the whole issue wouldn't be there if the front-end did a better job of validating input values. Front end is being used by differnt continents and letting users to input "." or "," in numeric value fiels. In asian countries to enter value 900000000 users sometime input something like '900,000,000' in European countries user input is like '900.000.000' and if the figure actually contains a decimal value then users would input '900000000.00'. I guess there is no solution to issue other than fixing the front end and not letting user to enter "," or "." if it's not a decimal value.
At this point we have decided not to handle conversion error and have asked end users to take a look at all value that have "." or "," and replace them with right values.
Thanks a lot for your inputs and suggestions.
No comments:
Post a Comment