Re: How to best match strings

I think I found a solution since the best match is the string with the most characters. The query will be like this: select field_str from test
where input_str like field_str || '%'
and field_str = (select MAX(field_str) from test

                  where input_str like field_str || '%');

Thanks you all for your ideas. It was helpful.

jusungyang_at_yahoo.com (Jusung Yang) wrote in message news:<42ffa8fa.0208241034.7bdac973_at_posting.google.com>...
> You can try something like this, just replace the string in substr
> with your input string variable:
>
>
> SQL> select * from test5;
>
> C1
> -------
> abcde
> abftpli
> bcet
> abcdefg
> ab
>
> SQL> select * from (
> 2 select t1.c1 from (
> 3 select c1, (CASE WHEN substr(c1,1,7)=substr('ab',1,7) THEN 7
> 4 WHEN substr(c1,1,6)=substr('ab',1,6) THEN 6
> 5 WHEN substr(c1,1,5)=substr('ab',1,5) THEN 5
> 6 WHEN substr(c1,1,4)=substr('ab',1,4) THEN 4
> 7 WHEN substr(c1,1,3)=substr('ab',1,6) THEN 3
> 8 WHEN substr(c1,1,2)=substr('ab',1,2) THEN 2
> 9 WHEN substr(c1,1,1)=substr('ab',1,1) THEN 1
> 10 END) as matched
> 11 from test5) t1 where t1.matched is not null order by t1.matched
> desc
> 12 ) where rownum <2;
>
> C1
> -------
> ab
>
> SQL>
>
>
> SQL> select * from (
> 2 select t1.c1 from (
> 3 select c1, (CASE WHEN substr(c1,1,7)=substr('abcdefg',1,7) THEN 7
> 4 WHEN substr(c1,1,6)=substr('abcdefg',1,6) THEN 6
> 5 WHEN substr(c1,1,5)=substr('abcdefg',1,5) THEN 5
> 6 WHEN substr(c1,1,4)=substr('abcdefg',1,4) THEN 4
> 7 WHEN substr(c1,1,3)=substr('abcdefg',1,6) THEN 3
> 8 WHEN substr(c1,1,2)=substr('abcdefg',1,2) THEN 2
> 9 WHEN substr(c1,1,1)=substr('abcdefg',1,1) THEN 1
> 10 END) as matched
> 11 from test5) t1 where t1.matched is not null order by t1.matched
> desc
> 12 ) where rownum <2;
>
> C1
> -------
> abcdefg
>
>
> SQL> select * from (
> 2 select t1.c1 from (
> 3 select c1, (CASE WHEN substr(c1,1,7)=substr('bcetg',1,7) THEN 7
> 4 WHEN substr(c1,1,6)=substr('bcetg',1,6) THEN 6
> 5 WHEN substr(c1,1,5)=substr('bcetg',1,5) THEN 5
> 6 WHEN substr(c1,1,4)=substr('bcetg',1,4) THEN 4
> 7 WHEN substr(c1,1,3)=substr('bcetg',1,6) THEN 3
> 8 WHEN substr(c1,1,2)=substr('bcetg',1,2) THEN 2
> 9 WHEN substr(c1,1,1)=substr('bcetg',1,1) THEN 1
> 10 END) as matched
> 11 from test5) t1 where t1.matched is not null order by t1.matched
> desc
> 12 ) where rownum <2;
>
> C1
> -------
> bcet
>
>
> gloai_at_hotmail.com (Loai Garelnabi) wrote in message news:<804a1aa9.0208232055.da8cb6e_at_posting.google.com>...
> > I'm sorry, but I forgot to mention that I need only the best macth
> > string, e.g., if the table contains 'hell' and 'hello', and the input
> > was 'hello', the query should only return 'hello'.
> > The other thing to keep in mind is that the input is of variable
> > length, between 2 and 7 characters. This is forcing me at the moment
> > to run multiple queries with different substrings of the input string.
> > My goal is to minimize the number of queries I'm using to identify the
> > best match.
Received on Sun Aug 25 2002 - 07:37:42 CEST