Post reply

convert varchar to float

  • 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

  • June 27, 2005 at 2:29 am

    #568514

    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 5:17 am

    #568529

    declare @v as varchar(10)

    set @v = '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/

  • June 27, 2005 at 6:19 am

    #568542

    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

    #568545

    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

    #568906

    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

    #568923

    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

    #568934

    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

    #568942

    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

    #568953

    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

  • June 28, 2005 at 8:58 am

    #568958

    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

    #568996

    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