CODEWITHSUNDEEP
sqloracle-database
ELBARTO
ELBARTO

Reputation: 13

Comparing string values within a table

Is there any way to compare two columns with strings to each other, and getting the matches?

I have two columns containing Names, once with the Full Name the other with (mostly) just the Surname.

I just tried it with soundex, but it will just return if the values are almost similar in both columns.

SELECT * FROM TABLE
WHERE soundex(FullName) = soundex(Surname)

1   John Doe       Doe 
2   Peter Parker   Parker
3   Brian Griffin  Brian Griffin

with soundex it will only match the 3rd line.

Upvotes: 1

Views: 214

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

As far as I know there is nothing out of the box when matching becomes complicated. For the cases shown, however, the following expression would suffice:

where fullname like '%' || surname

Update

The main problem may be false positives:

  • The last name 'Park' appears in 'Peter Parker'. Above query solves this by looking at the full name's end.

Another problem may be upper / lower case as mentioned in the other answers (not shown in your sample data).

  • You want the last name 'PARKER' match 'Peter Parker'.

But when looking at the strings case insensitively, another problem arises:

  • The last name 'Strong' will suddenly match 'Louis Armstrong'.

A solution for this is to add a blank to make the difference:

where ' ' || upper(fullname) like '% ' || upper(surname)
  • ' LOUIS ARMSTRONG' like '% STRONG' -> false
  • ' LOUIS ARMSTRONG' like '% ARMSTRONG' -> true
  • ' LOUIS ARMSTRONG' like '% LOUIS ARMSTRONG' -> true

Demo: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=0ac5c80061b4aeac1153a8c5976e6e54

Upvotes: 0

Jim Macaulay
Jim Macaulay

Reputation: 5165

Please use instring function,

SELECT * FROM TABLE
WHERE instr(Surname, FullName) > 0;


SELECT * FROM TABLE
WHERE instr(upper(Surname), upper(FullName)) > 0;

SELECT * FROM TABLE
WHERE upper(FullName) > upper(Surname);

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 143083

A simple option is to use instr, which shows whether surname exists in fullname:

SQL> with test (id, fullname, surname) as
  2    (select 1, 'John Doe'     , 'Doe'           from dual union all
  3     select 2, 'Peter Parker' , 'Parker'        from dual union all
  4     select 3, 'Brian Griffin', 'Brian Griffin' from dual
  5    )
  6  select *
  7  from test
  8  where instr(fullname, surname) > 0;

        ID FULLNAME      SURNAME
---------- ------------- -------------
         1 John Doe      Doe
         2 Peter Parker  Parker
         3 Brian Griffin Brian Griffin

Another option is to use one of UTL_MATCH functions, e.g. Jaro-Winkler similarity which shows how well those strings match:

SQL> with test (id, fullname, surname) as
  2    (select 1, 'John Doe'     , 'Doe'           from dual union all
  3     select 2, 'Peter Parker' , 'Parker'        from dual union all
  4     select 3, 'Brian Griffin', 'Brian Griffin' from dual
  5    )
  6  select id, fullname, surname,
  7    utl_match.jaro_winkler_similarity(fullname, surname) jws
  8  from test
  9  order by id;

        ID FULLNAME      SURNAME              JWS
---------- ------------- ------------- ----------
         1 John Doe      Doe                   48
         2 Peter Parker  Parker                62
         3 Brian Griffin Brian Griffin        100

SQL>

Feel free to explore other function that package offers.

Also, note that I didn't pay attention to possible letter case differences (e.g. "DOE" vs. "Doe"). If you need that as well, compare e.g. upper(surname) to upper(fullname).

Upvotes: 1

Related Questions