CODEWITHSUNDEEP
mysql
Benshack
Benshack

Reputation: 137

mySQL remove duplicate name from two columns in one column

Many thanks in advance.

I have two columns in my table:
Table: contactinfo
Column: Hometown, MailingAddress

Columns Hometown and MailingAddress contain duplicate (ugh!) information, where the Hometown lists "city", the MailingAddress lists "1234 Street, City"

I'd like to remove the city from MailingAddress wherever it matches the Hometown so that I can go from:

Hometown: City
MailingAddress: 1234 Street, City

to

Hometown: City
MailingAddress: 1234 Street,

I do not want to remove the "," as some of the MailingAddress entries have comma's in the street address information.

Once again, tonnes of thanks to you fantastic people.

Upvotes: 1

Views: 177

Answers (3)

fthiella
fthiella

Reputation: 49049

I would use this:

UPDATE contactinfo
SET MailingAddress = replace(MailingAddress, CONCAT(', ', Hometown), ',')
WHERE MailingAddress LIKE CONCAT('%, ', Hometown)

this will select all rows in which MailingAddress ends with , City, and replace every occurence of , City with just a , (so you have to make sure that , City only occurs once, at the end of the string).

Upvotes: 2

Teodor Blazhev
Teodor Blazhev

Reputation: 56

I don't know what script you are using but you need to first get the matching rows, then for each row use string functions in the script to remove the duplicate and then update the corresponding row.

query to select by your criteria (if i understood you correctly):

SELECT * FROM contactinfo WHERE MailingAddress LIKE CONCAT('%', Hometown)

Upvotes: 0

Bryan Allo
Bryan Allo

Reputation: 696

Take a look at MySQL String functions: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

Assuming you've identified the offending records, You should be able to update a new city and address field (use a temp field so as not to alter your source data). You can do something like this:

UPDATE contactinfo
SET new_MailingAddress = SUBSTRING_INDEX(MailingAddress, ',' 1) 
WHERE id IN [SELECT offending records]

Not the most elegant but should give you a starting point. Check out all the other string functions to see which ones best suit your needs. I believe you can also do some Regular Expression for more problematic data.

Good Luck.

Upvotes: 0

Related Questions