Re: How to best match strings
Date: 24 Aug 2002 11:34:39 -0700
Message-ID: <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
SQL> select * from (
C1
SQL>
SQL> select * from (
abcde
abftpli
bcet
abcdefg
ab
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;
ab
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 Sat Aug 24 2002 - 20:34:39 CEST