Foxpro V9 query to compare two columns

I have two foxpro tables that I am trying to compare.  The purpose is to identify a string of words that is potentially in the other table.

For example:  in my first table I have a striped number

Striped number (table 1)     Big Description (table2)
1234GXT                                 ledgecounter1234GXTxxs

I want to find all items where the striped number in table 1 exists anywhere in table 2.  What is the best way to do this?
appelonia21Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jharkinsCommented:
SELECT * FROM table1 join table2 ON ALLTRIM(table1.str_number) $ ALLTRIM(table2.big_description)

Will select all records where str_number in table 1 is found inside big_description in table 2

For a large number of records make sure there are indices on both fields or it make take a long time.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pcelbaCommented:
Just a small fix:
Indexes do not help in above query because VFP Rushmore cannot process $ operator.
Also the result will contain duplicate records if the stripped number from table 1 will be in several records of table 2.

The easiest fix is to use DISTINCT keyword:
SELECT DISTINCT * FROM table1 join table2 ...

Possible improvements:
1) If the Stripped number is always in uppercase then you may filter out lowercase letters:

SELECT * FROM Table1 ;
  WHERE str_number IN (SELECT PADR(CHRTRAN(big_descr, "abcdefghijklmnopqrstuvwxyz", ""), <n>) ;
 FROM Table2)

Replace the <n> by the str_number column width.

2) You could even create index on PADR(CHRTRAN(big_descr, "abcdefghijklmnopqrstuvwxyz", ""), <n>) on Table2 and then use the full strength of the JOIN optimization:

SELECT DISTINCT * FROM table1 ;
  INNER JOIN table2 ON table1.str_number = PADR(CHRTRAN(Table2.big_descr, "abcdefghijklmnopqrstuvwxyz", ""), <n>)

If you don't create above index VFP will create it itself if it decides so.

Standard SQL language would use LIKE operator which is not optimizable in this case.
0
appelonia21Author Commented:
I get this error when I try to complete the suggested query:  appdata\local\temp\0000evwp0062.temp is too large?
0
appelonia21Author Commented:
For additional clarification,  I have over 45000 items in table 1 that I want to see if the string exists anywhere in table 2.  Table 2 has over 400,000 items
0
pcelbaCommented:
Three queries are suggested above. Does the error appear in all of them (if all of them are applicable)?

Did you try to create the index on PADR(CHRTRAN(big_descr, "abcdefghijklmnopqrstuvwxyz", ""), <n>) on Table2 ?

Of course, the index creation is useful when your data follow the rule of lower/upper case mentioned in my answer.

The last hint:
You don't need to   SELECT * FROM Table1 ...
but just   SELECT str_number FROM Table1 ...
or   SELECT DISTINCT str_number FROM Table1 ...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FoxPro

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.