Hi All GURUs..
Thanks in advance for looking at this issue and helping me out..
Issue:
1. Search for LOGISTICS MASTER LLC – returns as expected, however search for LOGISTIC
MASTERS LLC does not return at all
2. Search for D AND D CONSUTLING – returns no records, however we have D & D CONSULTING on record, AND & conversion
3. REGAL PROPERTIES, LLC. Search does not return any records we do have a REGAL PROPERTIES, INC in the table
(which did not return in the customers search). However, if we search for REGAL PROPERTIES, it will return the entity REGAL PROPERTIES, INC.
Problem is that when customers are doing name search, we require them to use the business structure suffix (LLC, LP, INC, etc.)
4. Customer searches for The Mason Jar Café and no results are returned. We have an entity named MASON JAR CAFÉ, which did not return in the search.
5. search for THE REALM INCORPORATED, returns no records. REALM, LLC and THE REALM OF RC, LLC is in our database
6. Search for TRUE BLUE TILE AND STONE returns no rows. However, we currently have a TRUE BLUE TILE & STONE, INC on record
7. They wanted CLEAN TEAM llc. We already have THE CLEAN TEAM LLC on file but it was not returned in the search.
8. Customer searched DYNAMIC PROPERTIES, LLC . We already have DYNAMIC PROPERTIES, INC in table.
Logic used so far ..
create or replace FUNCTION letters_func2
(p_string IN VARCHAR2)
RETURN VARCHAR2
AS
v_string VARCHAR2(4000);
v1 varchar2(4000) ;
BEGIN
v1 :='A-Z0-9%>&-{}_<()*|";:?+=[-^!@#$''';
FOR i IN 1 .. LENGTH (p_string)
LOOP
IF REGEXP_LIKE (SUBSTR (p_string, i, 1),'['||v1||']', 'i') OR
SUBSTR (p_string, i, 1) = '-' THEN
v_string := v_string || SUBSTR (p_string, i, 1) || ',';
END IF;
END LOOP;
v_string := RTRIM (v_string, ',');
RETURN v_string;
END letters_func2;
create or replace FUNCTION letters_func3
(p_string IN VARCHAR2)
RETURN VARCHAR2
AS
BEGIN
RETURN
REPLACE (REPLACE (REPLACE
(REPLACE (REPLACE
(REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE
(REPLACE (REPLACE(REPLACE(REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE
(REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE
(letters_func2 (p_string),'$', '\$')
, '&', '\&')
,'(', '\(')
, ')', '\)')
, '/', '\/')
, '!', '\!')
, '-', '\-')
, '#', '\#')
, ':', '\:')
, '|', '\|')
, '?', '\?')
, '@', '\@')
, '''', '\''')
, '"', '\"')
, ']', '\]')
, '[', '\[')
, '+', '\+')
, '^', '\^')
, '*', '\*')
, '_', '\_')
, ';', '\;')
, '<', '\<')
, '>', '\>')
, '{', '\{')
, '}', '\}')
, '%', '\%')
, '=', '\=')
, ',,,', ',')
, ',,', ',');
END letters_func3;
/
BEGIN
CTX_DDL.CREATE_PREFERENCE ('letters_corp_datastore2', 'MULTI_COLUMN_DATASTORE');
CTX_DDL.SET_ATTRIBUTE
('letters_corp_datastore2',
'COLUMNS',
'letters_func2 (name) name');
CTX_DDL.SET_ATTRIBUTE ('letters_corp_datastore2', 'DELIMITER', 'NEWLINE');
CTX_DDL.CREATE_PREFERENCE ('letters_corp_lex2', 'BASIC_LEXER');
CTX_DDL.SET_ATTRIBUTE ('letters_corp_lex2', 'NUMGROUP', '~');
CTX_DDL.SET_ATTRIBUTE ('letters_corp_lex2', 'PRINTJOINS', '-&()|!@#$?''');
CTX_DDL.SET_ATTRIBUTE ('letters_corp_lex2', 'PUNCTUATIONS', ',.');
END;
/
--PL/SQL procedure successfully completed.
CREATE INDEX letters_Corp_index2 ON corp_names_data_near2 (name)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS
('DATASTORE letters_corp_datastore2
STOPLIST CTXSYS.EMPTY_STOPLIST
SYNC (ON COMMIT)
LEXER letters_corp_lex2
MEMORY 1G');
Query
Select score(1) score,
utl_match.jaro_winkler_similarity (upper(:P8_WORD_DESCRIPTION), upper(name)) jws,
GREATEST
(utl_match.jaro_winkler_similarity (upper(:P8_WORD_DESCRIPTION), upper(name)),
SCORE(1)) order1,
name
FROM corp_names_data_near2,
(SELECT letters_func3 (:p8_word_description) search_string
FROM DUAL)
WHERE CONTAINS
(NAME,
'<query>
<textquery>
<progression>
<seq>' || REPLACE (letters_func3 (:p8_word_description), ',', ' ') || '</seq>
<seq>' || REPLACE (letters_func3 (TO_CHAR (TO_DATE (NVL (DECODE (REGEXP_REPLACE (:P8_WORD_DESCRIPTION, '[^0-9]', ''), 0, NULL), '5373484'), 'J'), 'JSPTH')), ',', ' ') || '</seq>
<seq>NEAR((' || letters_func3 (:p8_word_description) || '),1,TRUE)</seq>
<seq>NEAR((' || letters_func3 (:p8_word_description) || '),100,TRUE)</seq>
<seq>' || letters_func3 (:p8_word_description) || '</seq>
</progression>
</textquery>
</query>',1)> 0
order by order1 desc;
/
AS you see I have used NEAR function, since my data has lot of special character in data use to replace it with comman so that it does return value based on function.
Not sure how to handle the Conversion of And to & or ignore INC LLC etc in search..
Thanks for your help