CODEWITHSUNDEEP
mysql
jamesthollowell
jamesthollowell

Reputation: 1712

How can I replace a field with a similar result in MySQL

Unfortunately, I have to deal with a lot of user submitted data, text fields rather than option boxes. I have imported it into my MySQL database as strings. I do all this to be able to run statistics quickly on the data like top 10 most common companies. The problem I have run into is that some of the rows have slightly different names for the same companies. For example:

Is there some fairly straightforward MySQL command or external tool that will allow me to go through and combine these sort of rows. I know how to use REPLACE(), but I don't think it has the power to do this simply. Correct me if I'm wrong!

Upvotes: 0

Views: 31

Answers (1)

Hatem Jaber
Hatem Jaber

Reputation: 2402

Taking this example:

Brasfield & Gorrie, LLC VS Brasfield and Gorrie

Assuming that I want to keep the first one, I would find all records that have the ID of the second one and update them to use the first, assuming that this table that has these titles also has an ID field for each one.

You would create a page in PHP that will allow you to administer this with mouse clicks, but it will require regular pruning since you allow users to enter this data. For future entries, you can try to apply the Levenshtein Distance and try to provide a suggestion based on available similar matches so that you can help guide the users to something that already exists rather than a new db entry.

Upvotes: 1

Related Questions