Home » Server Options » Text & interMedia » Problem with Fuzzy Query (10g)
Problem with Fuzzy Query [message #350357] Wed, 24 September 2008 16:59 Go to previous message
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
 
Read Message
Read Message
Previous Topic: CONTEXT INDEX and Tune LIKE clause (merged by bb and mc)
Next Topic: Near operator with Ctxcat index (split from CTXCAT / CONTEXT index by bb)
Goto Forum:
  


Current Time: Thu Mar 28 20:53:56 CDT 2024