none
character fields to numeric

    Question

  • I have a table with some fields like as below which are characters. i want to convert them to numeric and store them in another table.

    can someone assist me please?

    field_1                      field_2                      field_3

    000000057770.00 000000057770.00 000000000000.00

    000000154948.00 00000-279204.00  000000434152.00

    000000069766.00 000000016928.00 000000052838.00

        

    Friday, November 24, 2006 8:50 AM

Answers

  • Simple way would be:

    SELECT * from myTable INTO TABLE newTable
    ALTER TABLE newTable ;
     alter COLUMN field_1 b(2) ;
     alter COLUMN field_2 b(2) ;
     alter COLUMN field_3 b(2)

    However your data as I see it has some content questionable (ie: 00000-279204.00). You might need to do some processing based on its meaning. ie: If it meant -279204:

    SELECT * from myTable INTO TABLE newTable
    ALTER TABLE newTable ;
     alter COLUMN field_1 b(2) ;
     alter COLUMN field_2 b(2) ;
     alter COLUMN field_3 b(2)

    SELECT * from myTable INTO TABLE newTable

    FOR ix=1 TO FCOUNT()
      lcField = FIELD(m.ix)
      replace ALL (m.lcField) WITH TRANSFORM(EVALUATE(&lcField))
    ENDFOR

    ALTER TABLE newTable ;
     alter COLUMN field_1 b(2) ;
     alter COLUMN field_2 b(2) ;
     alter COLUMN field_3 b(2)

    Friday, November 24, 2006 9:34 AM

All replies

  • Simple way would be:

    SELECT * from myTable INTO TABLE newTable
    ALTER TABLE newTable ;
     alter COLUMN field_1 b(2) ;
     alter COLUMN field_2 b(2) ;
     alter COLUMN field_3 b(2)

    However your data as I see it has some content questionable (ie: 00000-279204.00). You might need to do some processing based on its meaning. ie: If it meant -279204:

    SELECT * from myTable INTO TABLE newTable
    ALTER TABLE newTable ;
     alter COLUMN field_1 b(2) ;
     alter COLUMN field_2 b(2) ;
     alter COLUMN field_3 b(2)

    SELECT * from myTable INTO TABLE newTable

    FOR ix=1 TO FCOUNT()
      lcField = FIELD(m.ix)
      replace ALL (m.lcField) WITH TRANSFORM(EVALUATE(&lcField))
    ENDFOR

    ALTER TABLE newTable ;
     alter COLUMN field_1 b(2) ;
     alter COLUMN field_2 b(2) ;
     alter COLUMN field_3 b(2)

    Friday, November 24, 2006 9:34 AM
  •  

    Thanks for the help but i still have a problem.  The table was updated alright but the negative value were all replaced with zeros.

     

    What do i do?

    Monday, November 27, 2006 7:08 AM
  • Oh I thought you understood the code and corrected the typo:

    SELECT * from myTable INTO TABLE newTable

    FOR ix=1 TO FCOUNT()
      lcField = FIELD(m.ix)
      replace ALL (m.lcField) WITH TRANSFORM(EVALUATE(&lcField))
    ENDFOR

    ALTER TABLE newTable ;
     alter COLUMN field_1 b(2) ;
     alter COLUMN field_2 b(2) ;
     alter COLUMN field_3 b(2)

    Monday, November 27, 2006 12:13 PM
  •  

    Yes did it as stated but the negatitives showed as zeros. However i did it this way and it worked.

     mtot = TRANSFORM(EVALUATE(field_1))
     mnth = TRANSFORM(EVALUATE(field_2))
     marr = TRANSFORM(EVALUATE(field_3))

    then replaced the variables in amother field


     REPLACE field_4 WITH val(mtot)
     REPLACE field_5 WITH val(mnth)
     REPLACE field_6 WITH val(marr)

    i'm using vfp6


     

    Tuesday, November 28, 2006 7:50 AM
  •  

    I did this also and it worked. Just changed the FieldType , FieldWidth  and the Precision as below

     

     

    SELECT * from mytable INTO TABLE new_table

    FOR ix=1 TO FCOUNT()
     lcField = FIELD(m.ix)
     REPLACE ALL (m.lcField) WITH TRANSFORM(EVALUATE(&lcField))
    ENDFOR

    ALTER TABLE new_table ;
     alter COLUMN field_1  N(15 , 2);
     alter COLUMN field_2  N(15 , 2) ;
     alter COLUMN field_3  N(15 , 2)

     

    Tuesday, November 28, 2006 8:09 AM