Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

FoxPro comparing columns

Posted on 2016-10-29
2
Medium Priority
?
165 Views
Last Modified: 2016-11-02
I am looking for a command that will compare two columns and flag for deletion items where similar words exist between the two.  Here is an example:

MFG1                                                        MFG2
USA BLUE BOOK APPROVED                USA BLUE BOOK
FRIO TEMP LAB                                       USA BLUE BOOK
CORNING                                                 USA BLUE BOOK

In these examples the command would flag the first row because the same words appear in both MFG columns.

Thank you,
0
Comment
Question by:appelonia21
2 Comments
 
LVL 43

Accepted Solution

by:
pcelba earned 2000 total points
ID: 41865321
You must define exactly what means "the same words appear in both MFG columns". Is the word order significant?

Your example just shows the shorter text is equal to the beginning of the longer text but that's not sufficient definition most probably but to provide a command which will solve it is possible, of course.

The following command will mark all such records for deletion:

SET EXACT OFF
DELETE FOR TRIM(mfg1) = TRIM(mfg2) OR TRIM(mfg2) = TRIM(mfg1)

You could also check whether the shorter text is contained inside the longer text at any position:

DELETE FOR TRIM(mfg1) $ mfg2 OR TRIM(mfg2) $ mfg1  

If you issue
SET COLLATE TO "GENERAL"
then the comparison will be case insensitive.

All above commands do not care about words they compare text strings so  FOX  =  FOXPRO.
If you need to compare whole words then let us know.
0
 
LVL 12

Expert Comment

by:jrbbldr
ID: 41870340
Pavel is correct in that we can only guess what you mean when you say:
the same words appear in both MFG columns

How many of the "same words"?
* 1 word existing in both columns?
* A 2 sequential word phrase in common?
* The entire phrase from one column in the other column?
* Or what?

Or even more un-certain:
similar words exist between the two

How are "similar words" determined?
* Same word mis-spelled?
* Words that have the same meaning?
* Or what?

Pavel has given you a suggestion for finding if the complete contents of one column are in the other column.
But that may not be what you need, depending on the specifics of what you are looking to discover.

Good Luck
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Visual FoxPro (short VFP) is a programming language with it’s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: http://msdn.microsoft.com/en-us/vfoxpro/default.aspx (http://msd…
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Integration Management Part 2
Loops Section Overview

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question