Home » Server Options » Text & interMedia » Problem with Fuzzy Query (10g)
Problem with Fuzzy Query [message #350357] |
Wed, 24 September 2008 16:59 |
formsdev
Messages: 6 Registered: June 2005
|
Junior Member |
|
|
Hi,
I wrote the following query.
my indexes look like this. I have the lexer so that it takes away any . - , etc.
v_indexscript := 'create index fuzzy_idx_addr1 on gp(address1) indextype is ctxsys.context parameters (''WORDLIST Stem_Fuzzy_pref LEXER Fuzzy_Lexer'')';
c:= dbms_sql.open_cursor;
dbms_sql.parse(c,v_indexscript, dbms_sql.native);
n := dbms_sql.execute(c);
dbms_sql.close_cursor(c);
I have similar indexes on all the columns of my where clause.
This is the SQL where I pass everything as parameters including the score and variations.
select col1, col2 ......score(1)
from gp
where CONTAINS(firstname, 'fuzzy('||pFirstname||', '||pScore||','||pVariants||',weight)',1)>0
and CONTAINS(lastname, 'fuzzy('||pLastname||', '||pScore||','||pVariants||', weight)',2)> 0
and (pAddress1 is null or CONTAINS(address1, 'fuzzy('||replace(paddress1,' ','')||', '||pScore||','||pVariants||', weight)',3)>0)
and (pAddress2 is null or CONTAINS(address2, 'fuzzy('||replace(pAddress2,' ','')||', '||pScore||','||pVariants||', weight)',4)>0)
and (pCity is null or CONTAINS(city, 'fuzzy('||replace(pCity,' ','')||', '||pScore||','||pVariants||', weight)',5)>0)
and (pState is null or CONTAINS(state, 'fuzzy('||pState||', '||pScore||','||pVariants||', weight)',6)>0)
and (pZip is null or CONTAINS(zipcode, 'fuzzy('||pZip||', '||pScore||','||pVariants||', weight)',7)>0)
and (pPhone is null or CONTAINS(phone1, 'fuzzy('||replace(pPhone,' ','')||', '||pScore||','||pVariants||', weight)',8)>0)
and (pEmail is null or CONTAINS(email, 'fuzzy('||replace(pEmail,' ','')||', '||pScore||','||pVariants||', weight)',9)>0) ;
** I am using replace because the lexer doesnt seem to take away any spaces.
My table has this data
First Last addr1 addr2 city state zip phone email
SS VV 21.First St Los angeles CA 90001 123-456-7890 ss@yahoo.com
SS VV
SS VV
SS VV
I would like the above query to return all the 4 rows when I pass it SS,VV and 21.First St as pFirstname, pLastname and pAddress1.
Is there a way to do that?
thanks
|
|
|
Goto Forum:
Current Time: Thu Mar 28 20:53:56 CDT 2024
|