On Wed, 2 Jul 2008 00:45:11 +0000 (UTC), "Carlos Nunes-Ueno"
<sullascl@[EMAIL PROTECTED]
> wrote:
Was that a real example? It seems difficult to come up with consistent
rules that convert "The Yummy and Tasty Waffle Cor****ation" into
"TASTYWAFFLE". Something like "Take the 4th and 5th word, and omit the
spaces"? Can you tell us what kinds of rules you're applying for the
conversion?
I have had good success with the Ratcliff/Obershelp algorithm that
returns a similarity (a number between 0 and 1) between two strings. I
checked and for your two company names the similarity is 0.75. Using
some cutoff value you can narrow down the most similar companies and
bunch them up that way.
We recently implemented this algorithm as a .Net assembly in SQL
Server 2005, and it is very fast. 10,000 comparisons in way less than
1 second.
I'm not at all convinced RegEx is the ticket here.
-Tom.
>I have a fairly large table (700,000 rows or so) that I'd like to run a
>process. However, the procedure we have right now was designed with
>tables of more like 20,000 rows and isn't able to handle it. Access will
>always crash before it can complete.
>
>Some background: the procedure is used in the process of data cleanup and
>is designed to process company names into a standardized form, so that we
>can use it to confirm data across various datasets. The procedure takes
>input like, for example, "The Yummy and Tasty Waffle Cor****ation" or
>"Yummy & Tasty Waffle, Incor****ated" and turns both into "TASTYWAFFLE".
>We can then sort, link, and filter, etc. on this field along with others
>to see if there are duplicates or check if companies that have different
>IDs are in fact the same company.
>
>Specifically, another procedure takes a specified table and field and
>creates a new field, filling it with the contents of the original field.
>That procedure then p***** a DAO recordset and the name of the new field
>to the main procedure which then performs 11 operations to arrive at the
>"TASTYWAFFLE" stage. Filling the new field is clearly double work and
>I'll be triming that part out.
>
>Now for my specific questions: Currently, the procedure takes the whole
>contents of the field at once, and uses a lot of InStr, Mid, Left, and
>Right functions to perform all of the operations, then moves on to the
>next row. It seems more direct to just read character by charater until
>I have a complete word (i.e. I hit a space or other delimiter), process
>that bit, then move on to the next part of the field. Which of these
>approaches is more efficient? Also, where could I go to find some
>guidelines on writing the most scalable VBA code? I know Access has
>limitations, but I'd like to be limited by those and not by our own
>inefficiencies.
>
>Thanks in advance,
>
>Carlos
|
