Hello All gurus-
I am using Jaro_winkler_Similarity and edit_distance to fetch my search results.
I am running into a snag where it only returns the data if it begin with that word but if its in the middle or at the end it does not recognize it.
SELECT
vcat_id
, name
, nationality_country
, sc
, sc1
, CASE
WHEN :p6_nationality_country IS NULL
OR nationality_country IS NULL THEN
sc
ELSE
round ((sc * 2 + sc1) / 2)
END score
FROM
(
SELECT
entities.entity_id vcat_id
, entities.entity_name name
, 'Entity' "Type"
, entities.country nationality_country
, utl_match.jaro_winkler_similarity (lower (entities.entity_name), regexp_replace (lower (:p6_search), '[[:space:]]', '')) sc
, utl_match.edit_distance (:p6_nationality_country, entities.country) sc1
FROM
entities
UNION ALL
SELECT
individuals.individuals_id AS vcat_id
, individuals.first_name
|| ' '
|| nvl2 (individuals.middle_name, individuals.middle_name || ' ', '')
|| individuals.last_name
|| nvl2 (individuals.second_last_name, ' '
|| individuals.second_last_name
|| ' ', '') name
, 'Individuals' "Type"
, individuals.nationality nationality_country
, utl_match.jaro_winkler_similarity (lower (individuals.first_name
|| individuals.middle_name
|| individuals.last_name
|| individuals.second_last_name), regexp_replace (lower (:p6_search), '[[:space:]]',
'')) sc
, utl_match.edit_distance ((:p6_nationality_country), individuals.nationality) sc1
FROM
individuals
)
WHERE
CASE
WHEN :p6_nationality_country IS NULL
OR nationality_country IS NULL THEN
sc
ELSE
round ((sc * 2 + sc1) / 2)
END >= 75
AND instr (':'
|| nvl (:p6_nationality_country, nationality_country)
|| ':', ':'
|| nationality_country
|| ':') > 0
ORDER BY
score DESC;
sample data
Insert into EXPORT_TABLE (VCAT_ID,NAME,NATIONALITY_COUNTRY,SC,SC1,SCORE,"Type") values (13429,'Nova Scotia Inv. Ltda',null,89,-1,89,'Entity');
Insert into EXPORT_TABLE (VCAT_ID,NAME,NATIONALITY_COUNTRY,SC,SC1,SCORE,"Type") values (23091,'Nova Scotia Inversiones LTDA',null,87,-1,87,'Entity');
Insert into EXPORT_TABLE (VCAT_ID,NAME,NATIONALITY_COUNTRY,SC,SC1,SCORE,"Type") values (23386,'Nova Leasing',null,82,-1,82,'Entity');
Insert into EXPORT_TABLE (VCAT_ID,NAME,NATIONALITY_COUNTRY,SC,SC1,SCORE,"Type") values (126810,'Nova Cidade De deus','BR',82,-1,82,'Individuals');
Insert into EXPORT_TABLE (VCAT_ID,NAME,NATIONALITY_COUNTRY,SC,SC1,SCORE,"Type") values (13267,'NOA','RU',81,-1,81,'Entity');
Insert into EXPORT_TABLE (VCAT_ID,NAME,NATIONALITY_COUNTRY,SC,SC1,SCORE,"Type") values (120373,'Nobuaki Sato','JP',80,-1,80,'Individuals');
Insert into EXPORT_TABLE (VCAT_ID,NAME,NATIONALITY_COUNTRY,SC,SC1,SCORE,"Type") values (42662,'Novica Radovic','ME',80,-1,80,'Individuals');
Insert into EXPORT_TABLE (VCAT_ID,NAME,NATIONALITY_COUNTRY,SC,SC1,SCORE,"Type") values (20092,'Bank of Nova Scotia','CA',81,-1,81,'Entity');
If Search on Nova Scotia, my query doesn't return the row Bank of Nova scotia.
Kindly Suggest.