Deleting Formats

[dzdncnfsd]

I need to pull data from tables that contain formatted data into a table with no formatting. Dates are stored as 09/08/2000, I need them to be 09082000 in my new table. The same goes for social security numbers and phone numbers. How do I delete the unwanted characters?
Thanks. [sig]<p>Gladys Clemmer<br><a href=mailto:gladys.clemmer@fifsg.com>gladys.clemmer@fifsg.com</a><br><a href= > </a><br> [/sig]

 

[TomasDill]

Say, you have old table with dDate D field and new table with cDate C(8) field. Use following commands to transfer data:

Select OldTable
SCAN ALL
INSERT INTO NewTable (cDate) Values CHRTRAN(DTOC(OldTable.dDate),'/','')
ENDSCAN

Use CHRTRAN function for 1 character only. There are many other string processing functions in VFP.
In addition, specially for dates VFP have function 'DTOC' with second parameter 1 that returns string in format 'YYYYMMDD'.
[sig]<p>Vlad Grynchyshyn<br><a href=mailto:vgryn@softserve.lviv.ua>vgryn@softserve.lviv.ua</a><br>[/sig]

 

[dzdncnfsd]

Thank you, but what I would like to do is remove the formatting characters while I am creating the new table. Is it possible to do that in one step?
Thanks. [sig]<p>Gladys Clemmer<br><a href=mailto:gladys.clemmer@fifsg.com>gladys.clemmer@fifsg.com</a><br><a href= > </a><br> [/sig]

 

[TomasDill]

What you mean 'In one step'? IN one VFP command? No. In 2 commands.
1 command is SQL SELECT statement that creates cursor and formats data (columns are expressions like I already described).
2-nd command - just 'copy to' command to copy cursor into table.
[sig]<p>Vlad Grynchyshyn<br><a href=mailto:vgryn@softserve.lviv.ua>vgryn@softserve.lviv.ua</a><br>[/sig]

 

[jpugh]

You can do the conversion(s) as part of a SQL SELECT.

EX.
SELECT cdate AS CHRTRAN(DTOC(dDate),'/',''),newSSN AS CHRTRAN(ssn,'-',''),etc. FROM oldTable INTO TABLE newTable

Hoder=his helps.
[sig][/sig]

 

[dzdncnfsd]

What am I doing wrong? Here is my statement:

SELECT dobnew AS CHRTRAN(DTOC(dob),'/',''), newSSN AS CHRTRAN(na_sec,'-','') FROM finalname INTO TABLE il80name

I keep getting the &quot;unrecognized phrase or keyword&quot; error message. The field names in my existing table (finalname) are dob and na_sec.

Thanks again.
[sig]<p>Gladys Clemmer<br><a href=mailto:gladys.clemmer@fifsg.com>gladys.clemmer@fifsg.com</a><br><a href= > </a><br> [/sig]

 

[dzdncnfsd]

OK, I just figured it out. I had to change the order of the statement to:

SELECT CHRTRAN(DTOC(dob),'/','') as dobnew, CHRTRAN(na_sec,'-','')as newSSN FROM finalname INTO TABLE il80name

Thanks a lot. This will save me several hours of work today.



[sig]<p>Gladys Clemmer<br><a href=mailto:gladys.clemmer@fifsg.com>gladys.clemmer@fifsg.com</a><br><a href= > </a><br> [/sig]