June 26, 2005 at 9:16 pm
can anyone tell me how to convert varchar to float? i have 2 sql statements joined by a union all. the first statement's purchase price is float while the one from the second statement has a type of varchar. i need to convert them to float. would appreciate any suggestions/ideas =)
i tried converting the 2 to varchar and it worked but the output contains exponents in the numbers so i think i need to have them in floats. but i tried using convert and cast and im getting the error:
Error converting varchar to float.
ann
June 27, 2005 at 2:29 am
Hi Ann,
Could you please post the CAST and CONVERT statements that are actually giving you the error.
Have fun
Steve
We need men who can dream of things that never were.
June 27, 2005 at 6:19 am
I suggest checking the varchar data as the data content will most likely give you the 'Error converting varchar to float' error.
e.g. '-' or '.1' will give you the error
Far away is close at hand in the images of elsewhere.
Anon.
June 27, 2005 at 6:29 am
DECLARE @myCol VARCHAR(50)
DECLARE @myNum FLOAT
SET @myCol = '12.50'
SELECT @myNum = CASE WHEN ISNUMERIC(@myCol) = 1 AND @myCol <> '-' AND @myCol <> '.' THEN CONVERT(FLOAT, @myCol) ELSE 0 END
SELECT @myNum
SET @myCol = '-'
SELECT @myNum = CASE WHEN ISNUMERIC(@myCol) = 1 AND @myCol <> '-' AND @myCol <> '.' THEN CONVERT(FLOAT, @myCol) ELSE 0 END
SELECT @myNum
SET @myCol = '.1'
SELECT @myNum = CASE WHEN ISNUMERIC(@myCol) = 1 THEN CONVERT(FLOAT, @myCol) ELSE 0 END
SELECT @myNum -- I get 0.10000000000000001 here
Regards,
gova
June 28, 2005 at 7:56 am
Hello!
Just thought I should throw in my dime. There are more cases when the ISNUMERIC returns 1 for vaules that are NOT convertible to float...
Try for ex:
SELECT ISNUMERIC('$')
SELECT CONVERT(FLOAT, '$')
Could be good to do some additional checks...
//Hanslindgren
June 28, 2005 at 8:25 am
Hans - for the extra cents that you threw in, maybe Ann should go with checking ascii values....
SELECT @myNum = CASE WHEN ISNUMERIC(@myCol) = 1 AND ASCII(@myCol) >= 48 AND ASCII(@myCol) <= 57 THEN CONVERT(FLOAT, @myCol) ELSE 0 END
SELECT @myNum
**ASCII stupid question, get a stupid ANSI !!!**
June 28, 2005 at 8:36 am
Yeah, I thought about it But then it would have been a full Euro and not some cents
You will run into some trouble with decimal separators if you only allow numbers... (Remember that some computers are configured for dots as separators and some contain commas)
And then... What about multiple dots and commas? What about mixed commas and dots? And that about negative values?
June 28, 2005 at 8:40 am
Besides:
ASCII(@myCol) >= 48 works for a SINGLE character not multiple you may need more than that if parsing is needed
* Noel
June 28, 2005 at 8:53 am
June 28, 2005 at 8:58 am
I can't test this out right now but I was wondering if it would not be better to convert it to money datatype instead of float - after all, we ARE talking about price and this datatype is set to accept comma separators, decimal points, -ves et al....????????
Maybe Ann can play with this some to see if it works ?!
**ASCII stupid question, get a stupid ANSI !!!**
June 28, 2005 at 9:44 am
I most certainly agree.
Having the wrong data types are not just bad. It is worse then bad
* You miss out of some aspects of declarative integrity. I.e when you find out that the prices stored in Varchar are not possible to use as prices, you will have a problem.
* Performance goes down when using data types that use more space then required.
* In this case you will also have problem with SORTing on prices...
There is alot more to say but I think the picture was clear already from your initial problem
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply