1

I have a peculiar requirement in Oracle. There is a column in a table with different values but sounds similar for ex: Limited, Ltd, Ltd., Etc. But I need to treat these same and shouldn't treat as distinct values. I tried it with Decode as below but the catch here is the values are not known to us. Below is just an example.

Create table test_dup(col1 varchar2(25));

Insert into test_dup values('limited');
Insert into test_dup values('ltd.');
Insert into test_dup values('Inc');
Insert into test_dup values('incorporate');

Select distinct decode(col1, 'limited', 1, 'ltd.', 1, 'Inc', 2, 'incorporate', 2) from dual;

This works when the values are known but in my requirement, the values are unknown.

Please help me resolve this.

Thanks in advance, Savitha

Savitha
  • 405
  • 4
  • 15
  • 25
  • 2
    You could use the `soundex` function to match up similar-sounding values. Or, for a more powerful similarity method, see https://stackoverflow.com/questions/653157/a-better-similarity-ranking-algorithm-for-variable-length-strings – rd_nielsen Jul 27 '17 at 19:03
  • Strictly from a database perspective; you will have to map each definition by the users. Without proper mapping, any data can mean anything. I believe a similar approach can be taken by using front end algo using fuzzy match but not through the back end. – Isaiah3015 Jul 27 '17 at 20:26
  • @rd_nielsen, We tried it through Soundex but it didn't yield us the expected results accurately. We'll try to adopt the methods provided in the link you shared. Thanks for the help. – Savitha Jul 28 '17 at 00:14
  • @Isaiah3015, yes, we need it strictly from DB perspective. We won't be able to change the mappings now. We shall try to adopt the methods provided by rd_nielson, if possible. Thanks for the response. – Savitha Jul 28 '17 at 00:18

1 Answers1

0

I've found the way to do it through Oracle-SQL. It is through UTL_MATCH package.

SELECT utl_match.JARO_WINKLER_SIMILARITY('LTD.','LIMITED') FROM DUAL;

Regards, Savitha

Savitha
  • 405
  • 4
  • 15
  • 25