Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

FoxPro comparing columns

Posted on 2016-10-29
2
Medium Priority
?
149 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

DFW AZURE MEETUP TONIGHT FRI 6PM

We will be discussing what Azure Stack is, how does it fit into the suit of offerings that Azure has currently, and where can it fit into your organizations technology stack. We will also be discussing limitations of the platform while covering various applicable scenarios.

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…
By default Outlook 2016 displays only one time zone in the Calendar. The following article explains how to display two time zones in one calendar view.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

722 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