convert varchar to float

Question

Post reply

convert varchar to float

ann ramos

Right there with Babe

Points: 791
More actions
June 26, 2005 at 9:16 pm

#67202

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

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Ste_P SSCarpal Tunnel Points: 4430 More actions · Answer 1

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.

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 2

declare @v-2 as varchar(10)

set @v-2 = '12.50'

select cast(@v as float) * 10

will work

But I fell it is better to chaneg the price column to float as it will increase the performances

My Blog: http://dineshasanka.spaces.live.com/

David Burrows SSC Guru Points: 65142 More actions · Answer 3

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.

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 4

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

Hans Lindgren SSChampion Points: 10496 More actions · Answer 5

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

sushila SSC-Dedicated Points: 35293 More actions · Answer 6

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 !!!**

Hans Lindgren SSChampion Points: 10496 More actions · Answer 7

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?

noeld SSC Guru Points: 96590 More actions · Answer 8

Besides:

ASCII(@myCol) >= 48 works for a SINGLE character not multiple you may need more than that if parsing is needed

* Noel

Hans Lindgren SSChampion Points: 10496 More actions · Answer 9

I guess the most fool proof way of determining if it is convertible to float would be a regular expression (I.e something like ^(\+|-)?[0-9]*(\.[0-9]*)?$ . Unfortunatly that must be baked outside T-SQL

sushila SSC-Dedicated Points: 35293 More actions · Answer 10

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 !!!**

Hans Lindgren SSChampion Points: 10496 More actions · Answer 11

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