From: Robert Klemme <shortcutter@...>
Date: 2008-10-10T01:24:11+09:00
Subject: Re: Problem with comparing huge amount of strings

On 08.10.2008 10:13, Jan Fischer wrote:

> thank you very much for your hints. I think you both suggest using sth. 
> like the soundex-function to characterize each single row in a certain 
> way and then group by this chracteristic. (Like Brian Chandler suggests 
> in another post too, thanks Brian.)
> Fact is that I already tried the "soundex-solution", but the results 
> don't even come close to what I expected. I also tried to build the 
> metaphone-key of every string and group by that, but that wasn't enough 
> too.
> 
> I think a mix of your hints and what Ragev Satish suggests in his post 
> will prabably lead me into the right direction:

Yep, I agree.

> First step is to "normalize" my strings (strip "Ltd." etc.), build the 
> metaphone key and then write that back into the database instead of 
> calculating everything again for each comparison. Should be O(n), right? 

Yes, kind of.  In databases O calculus is not so important because SQL 
is descriptive and not procedural.  In other words, you have no control 
(in reality there is some control you can exert but it is important to 
keep in mind that SQL is not a programming language) over what 
operations the DB will execute in order to give you the result set you 
requested.  It is more important to care about access paths so you 
definitively want to index the result of this function.

You did not state which RDBMS you are using.  I know Oracle very well 
and SQL Server pretty well, so I speak only about the two.  Chances are 
that you can do similar things with other products.  In both cases you 
can define a function for this.  In SQL Server you can then store a 
calculated column which uses this function and in Oracle you can 
simulate the calculated column with a trigger or create a function based 
index.  Then you can query that calculated column / function efficiently 
and group by or order by that column.  If you add analytic SQL to the 
mix you can even do some nice additional tricks like counting per column 
value etc.

Cheers

	robert