Question: I have an application with a
unique key on these four columns:
My problem is that I have people who are duplicated with
misspellings in the names.
How can I detect duplicates row column values caused by
misspelled surnames and first names?
Answer: You can join a table against
itself to find non-matching rows, in this case matching birth dates
and first and middle names. This syntax will display all people who
have matching birth dates, first_name and middle_name,
with different last name values:
select
a.first_name||' '||a.middle_name
first_n_middle_name,
a.last_name
old,
a.created_date,
b.last_name
new,
b.created_date
from
person a,
person b
where
--
********************************************
--
display near matches on last name (surname)
--
********************************************
(
trunc(a.date_of_birth) = trunc(b.date_of_birth)
and
a.first_name = b.first_name
and
a.middle_name = b.middle_name
and
a.last_name <> b.last_name
);
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
