How to eliminate 'duplicates' in my database

I have a database with some lines duplicated as follows:

MRSDxxx xxx 29/1x/19xx x, CHURCH ROAD,OARE, FAVERSHAM,,ME13 0QA
MRSDxxx xxx 29/1x/19xx x CHURCH ROAD, OARE, FAVERSHAM,,, ME13 0QA

as you can see, the only different is the number and name of street are in separate columns in some records and all in one in others... everything else is identical

trying to figure out a query to find these

 

Hi, I have created a table and added duplicate record then against that I have created query please change the fields name according to your need, here is the query and link to solution, just change the * to T

h**p://www.sqlfiddle.com/#!2/481e9/3

select ip,logs,count(ip) as countofrecord from serversip
group by ip

Code (markup):

cheers

 

Hey,
Though both the rows contain identical information but have different column values so if you compare them by column values the problem will not be solved.

 

as per SQL is concern this is not duplicate. even a group by will not find these as duplicate count.

what i suggest you remove the " " and "," and replace it with empty strings "". Then do the comparison

 

please read question he asked to find them not to delete them... group by will find a record or column name that happens more than one time so when you count that it will show number records with similar data... please check that fiddler

 

did i mention "Delete" in my post? read my answer very well.

MRSDxxx xxx 29/1x/19xx x, CHURCH ROAD,OARE, FAVERSHAM,,ME13 0QA
MRSDxxx xxx 29/1x/19xx x CHURCH ROAD, OARE, FAVERSHAM,,, ME13 0QA

Based on his data, everything is the same , so i suggest concat all fields , then remove comma and space. then do the comparison.

 

If this is in one column this should work:

SELECT details
FROM something group by REPLACE(REPLACE(details,"," , "")," ", "" )

Code (markup):

http://sqlfiddle.com/#!2/81276/2


If the data is spread into multiple columns, this should work:

SELECT details,details2
FROM something group by
REPLACE(REPLACE(CONCAT(details,'',IFNULL(details2,'' ) ),"," , "")," ", "" )

Code (markup):

http://sqlfiddle.com/#!2/db5f09/1