I want to merge the duplicate names in my table or at least see the names that are unique and look alike

Question

I have a employee table with schema as follows:

Id Name Birthday DeathDay Startdate EndDate

The problem is that I have data as follows:

Bergh Celestin 06/09/1791 14/12/1861

Bergh Célestin 06/09/1791 14/12/1861

Bergh Francois 04/04/1958 11/12/2001

Bergh Jozef Francois 04/04/1958 11/12/2001

Now i want to merge these records as 1 as they are the same person how can i do that?

Also, if I just want to display the list of only those person from the table whose names are possibly same, like above, how can I do that?

I used:

select Distinct name,birthday,deathday from table but that is not good enough.

You are writing a SQL query and SQL does no magic, and it cant see faces like humans to identify that different names belong to same person. Your query does not work for the table you designed. — user1658435, May 19 '14 at 07:24
What do you mean with merge? What should be the result of the query. Please provide an example. — Jens, May 19 '14 at 07:25
The example is as follows: Bergh Francois 04/04/1958 11/12/2001 Bergh Jozef Francois 04/04/1958 11/12/2001 or there is a data entry error as well for example: Joseph Emanuael 03/04/1960 03/12/2010 Joseph Emanuael 04/03/1958 03/12/2010 both are the same person but have 2 different enteries and IDs first i want to display these persons and later merge them with a query as a single record. — Mouzzam Hussain, May 19 '14 at 07:30

score 0 · Answer 1 · edited May 23 '17 at 11:50

I would use a function (.NET or SQL) of sorts to remove the accents as per https://stackoverflow.com/a/12715102/1662973 and then group on that together with the dates. You will need to group on something, as essentially "Bergh Célestin" could actually be a different person to "Bergh Celestin".

Sample:

select 
    RemoveExtraChars(name)
    ,birthday
    ,deathday
from
    TABLE
group by 
    RemoveExtraChars(name)
    ,birthday
    ,deathday

score -2 · Answer 2 · answered May 19 '14 at 07:34

-2

For your second Question you can use SQL LIKE Operator:

SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern;

answered May 19 '14 at 07:34

PANKAJ kSHARMA

125
1
9

I want to merge the duplicate names in my table or at least see the names that are unique and look alike

2 Answers2