Remove trailing zero's and decimal point in Visual FoxPro 9

Question

I know this programming language is pretty obsolete, but my company still uses it and it's hard to find specific help with these kinds of things online because of it being unsupported. I searched for a few options on here but found nothing.

Here's what my data looks like: 8005551234.000000

Here's what I need my data to look like: 8005551234

The reason it's showing up with the trailing zero's is because I'm assuming when I imported the data into FoxPro via an excel add on the field was an integer field, and when it was converted to a dbf it needed to be a character field.

I've played around with the TRANSFORM function a bit, but I can't seem to figure it out.

My field name is "Phone" if that helps...

Thanks in advance for your support!

Answer 1

If the field "phone" doesn't have hyphens in it as commented by Frank in another answer, you can always strip by using LEFT( x, at()).. such as

replace Phone with left( phone, at( ".", phone ) -1 );
    for at( ".", phone ) > 0

If you have a format with decimals as place-holders for the phone, such as

1.800.TRY.MORE.

You might have to use the optional 3rd parameter of "AT()" function which indicates which "." instance you are interested in. In the example of 1.800... you could try

replace Phone with left( phone, at( ".", phone, 4) -1 );
    for at( ".", phone, 4 ) > 0

AT() function is basically

Look for "X" found in string "Y" and optionally, look for the "n" instance such as AT( X, Y, n )

Hope this helps.

Answer 2

Use the STREXTRACT() function. It handles the scenario where there might not be a decimal and the scenario where other character might exist. e.g.

? STREXTRACT("8005551234.000000", "", ".", 1, 2)  && returns 8005551234
? STREXTRACT("8005551234", "", ".", 1, 2)  && returns 8005551234
? STREXTRACT("800-555-1234", "", ".", 1, 2)  && returns 800-555-1234

Answer 3

If this is a numeric try this.

MyNewValue = INT(8005551234.000000)

or MyNewValue = INT(MyTable.MyFieldName)

If this is a string, try this:

MyNewValue = INT(VAL("8005551234.000000"))

or MyNewValue = INT(VAL(MyTable.MyFieldName))

To see what type it is, do this in command window:

USE myTableName
DISPLAY STRUCTURE

Answer 4

   *Make Numeric value to String value
   nilai=01230045600.00789000
   ?makestr(nilai) &&result => 1230045600.00789
   function makestr(nilai)
      cNum1=alltrim(str(int(nilai)))
      nNol=0
      for i=1 to len(cNum1)
        if right(left(cNum1,i),1)="0" then
          nNol=nNol+1
        endif
      endfor
      cnilai=str(nilai,20,10) 
      if nNol>0 then
        cnilai=alltrim(strtran(cnilai,"0"," ",nNol))
      else
        cnilai=alltrim(strtran(cnilai,"0"," "))
      endif
      cnilai=alltrim(strtran(cnilai," ","0"))
   return cnilai
  

Answer 5

Use STR() function. Example: 1. create a cursor with a number field representing the data you've imported and a string field for conversion 2. insert your numeric phone# 3. replace the numeric phone# with a string 4. browse the results

CREATE CURSOR xphone (nphone n(17,6), cphone c(10))
INSERT INTO xphone (nphone) VALUES (8005551234)
REPLACE cphone WITH STR(nphone)
BROWSE

• you can copy and paste the above code into a program window in vfp9, highlight it, right click and select Execute Selection and it will work for you.