Post reply

Convert text to number

  • June 23, 2011 at 8:16 am

    #242582

    I need to convert a column (Result) from text to float in a big table (about 150000 records). For example, the original data was stored in Result is mixed text and number, such as "1.33", "50", ">60", "not available"... I need to keep in Result is number like "1.33", "50"... How to code to finish this job?

  • June 23, 2011 at 8:22 am

    #1343064

    here is how i would do it:

    1.add a new float column to the table.

    2.UPDATE table set newcolumn = convert(float,oldcolumn) where isnumeric(oldcolumn) =1

    3. the columns that were non-numeric willb e null.....do you want to default them to zero, or leave null...

    4. drop the old column

    5. rename the new column

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 23, 2011 at 8:58 am

    #1343097

    Thank you, Lowell.

    It works great!

Viewing 3 posts - 1 through 2 (of 2 total)

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