APEX Oracle Compare Two Fields (varchar2 )-CodePudding

I want to compare two varchar2 fields and based on the percentage similarity to get this percentage as a result in my function, and the ID of this record from this table.

I have the table (SYMPTOMS), I also have the field Symptom_Descr (VARCHAR2) of this table and the variable v_symptom (varchar2) and I want to compare this variable with this field.

For example, this is my table:

The variable that I want to compare is: 'flashes the room lights 5 times'

I want as a result=

id
1	0%
2	0%
3	90%

Another example if the variable is 'washes her hands 7 times':

id
1	80%
2	0%
3	10%

The above percentages are not exact.

If the above cannot be done, then what can I do to find the similarities?

CodePudding user response：

You can use the UTL_MATCH package:

SELECT id,
       UTL_MATCH.EDIT_DISTANCE_SIMILARITY(
         symptom_descr,
         'flashes the room lights 5 times'
       ) AS ed_similarity,
       UTL_MATCH.JARO_WINKLER_SIMILARITY(
         symptom_descr,
         'flashes the room lights 5 times'
       ) AS jw_similarity
FROM  symptoms;

Which, for the sample data:

CREATE TABLE symptoms (id, symptom_descr) AS
SELECT 1, 'washes his hands constantly' FROM DUAL UNION ALL
SELECT 2, 'checks several times if he turned off the water heater' FROM DUAL UNION ALL
SELECT 3, 'flashes the room lights too many times' FROM DUAL;

Outputs:

ID ED_SIMILARITY JW_SIMILARITY

1 30 62

2 25 62

3 79 93

ID	ED_SIMILARITY	JW_SIMILARITY
1	30	62
2	25	62
3	79	93

db<>fiddle here