Janusz
4/1/2007 5:27:00 PM
On Apr 1, 1:41 am, "xyb" <xiangyua...@gmail.com> wrote:
> On 4?1?, ??2?38?, "Janusz" <jezow...@gmail.com> wrote:
>
>
>
> > Guys,
>
> > I am trying to figure out how to program an efficient sql query for
> > this scenario.
>
> > Let's say I have a table with strings like that: thisismyexample ,
> > thisismyanotherexample, lastexample
>
> > Then I have a table with dictionary words:
> > this,is,my,example,another,last,..... (all the english dictionary
> > words sorted alphabeticaly)
>
> > What I want to do is retrieve:
>
> > this is my example , this is my another example , last example
>
> > There might be cases where there are no matches or multiple matches -
> > when it happens, I don't care , I just want the fastest algorithm
>
> > Can anyone point me in the right direction? What do read? How to
> > implement it the best?
>
> > The onle way how I know how to do it would be use of LIKE operator
> > '%word%' but still not sure how exactly.
>
> > Thanks a lot!
>
> A complicated problem because of multi matches.
> a sample code i tested:)
> create table #lib
> (
> ID int identity(1,1),
> Word varchar(200)
> )
>
> insert into #lib
> select 'this'
> union select 'is'
> union select 'an'
> union select 'example'
>
> select * from #lib
>
> declare @str varchar(8000)
> select @str = 'thisisanexample'
>
> select @str = replace(@str,word,' '+word+' ') from #lib
> select @str
xyb. Thanks for your example! I haven't thought of that.
If we modify the replacement to: select @str = replace(@str,word,'')
from #lib
(So that if the string has only dictionary words then we get empty
string as the result)
The multiple matches are problem, but could be fixed by... .. Not
having a one big table of dictionary words, but having a table that
would group all the words with the same length...
Then the algorithm would replace the string with the words from the
longest table, then second longest..etc.. and if in the end we get an
empty string - then we get the match.
Would do you think?