July 11, 2008 at 5:24 pm
Hi!
I'm trying to do a global find and replace of a string of text throughout all tables and columns in my database. I have a solution for finding and replacing the text value when I get an exact match, but I'm finding a bunch of instances where the text was misspelled or typo'ed.
Example:
UPDATE company
SET name = replace(name, 'taskforce', 'blue team')
WHERE name like '%taskforce%'
or name like '%task force%'
Now, I can continue to add "or" conditions to my where clause, but I can't possibly think of all the misspellings and typos (there are some really, really weird ones like "taacks froce", etc).
I used the soundex function, but that didn't yield a better result than my list of "or" conditions.
Is there a better way to do this using RegEx or some other strategy or should I simply resign myself to this being a tedious cleanup process?
Thanks for the help!
rsobers
July 12, 2008 at 12:33 am
Hi Rob,
I am not sure what you are trying to do here.
Shouldn't the company names be unique?
If the update you are trying to do succeeds, you would have several companies with the same name.
But to answer the question: I would go for the manual solution.
You could assist yourself by ordering by various substrings of the name in search
to find misspellings - ORDER BY SUBSTR(name,x,500). For x you use numbers from 1 to 8 to find potential misspellings which are different in the first x-1 letters. Of course you can also apply various substring lenghts instead of 500.
This should be done in a SELECT and then afterwards correct the found misspellings manually through an explicit update.
Best Regards,
Chris Büttner
July 12, 2008 at 7:06 am
Thanks for the reply.
The example I provided was probably not a great one. The actual search and replace I'll be doing will be on data that should be repeated multiple times throughout many rows in a table but should be the same exact spelling each time.
I'll give your suggestion a shot to see if it makes things a bit easier.
I guess the best way to describe what I'm looking for would be a fuzzy search method.
Rob
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply