Post reply

IsNumeric/Converting data types

  • August 4, 2004 at 3:50 pm

    #108478

    I have a Field defined as a varchar.  I want to remove all values from this table where the value of the field is not numeric, and then copy this field to an Integer field.

    Update Table Set Field=Null Where IsNumeric(Field) = 0

    This works pretty well.

    Update Table Set IntegerField = Convert(int, VarCharField)

    This SQL String fails because in the original data, there is a value of '1E2', which seems to pass through the IsNumeric Function, but does not convert to an integer.

    Any help would be greatly appreciated.


    George Mastros
    Orbit Software, Inc.

  • August 4, 2004 at 4:17 pm

    #517731

    The 1E2 is a float data type and will not convert to an integer.

    Try:

    Update Table Set IntegerField = Convert(int, Round(VarCharField,4))

     

    Let me know how it goes.

  • August 4, 2004 at 4:26 pm

    #517734

    Thanks Peter.  The Round function resolved the problem.  Before your post, I tried...

    Update Table Set IntegerField=Convert(Int, Convert(Float, VarCharField))

    This also worked.  Converting to a float, and then to an integer just doesn't "feel" right.  I wonder which would give the better performance?


    George Mastros
    Orbit Software, Inc.

  • August 4, 2004 at 4:37 pm

    #517735

    I would imagine that 1 Function call is faster than 2.  I'm glad you've got a fix.

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

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