2

I need to do a find a replace but rather than find one word to replace with another I have about 16k words, so as an example; table 1 has two words apple,orange - table 2 has the contents of a book, i want to be able to replace all the words apple and orange that exists in table 2 and replace them with ##, is there a query that does this ? my actual table 1 has about 16k words and table 2 has 8 million words. most of the examples on google suggests i type the words to find but that is not possible when i have 16k words I am using mysql workbench and an absolute beginner

(from comment) table 1 has 16k names in a column - table 2 has emails and text in columns - i want to be able to censor out all the names in table 2 using the names in table one as a reference

5
  • First of all, welcome to the dba stack exchange. Your question doesn't give enough details. Are you looking to find 16k specific words out of millions in that Table 1, and finding 8million specific words out of tens of millions? Or do you just have to select all the words in table 1 and table 2 and replace them with ## ?
    – Chessbrain
    Dec 15, 2018 at 23:51
  • 1
    Are these "words" alone in some column? Or buried in a long text column?
    – Rick James
    Dec 16, 2018 at 0:30
  • you have words in columns?? or have full text of CLOB or what??
    – Nikhil S
    Dec 16, 2018 at 0:30
  • thank you all and thank you @chessbrain for your welcoming message - further explaining, table 1 has 16k names in a column - table 2 has emails and text in columns - i want to be able to censor out all the names in table 2 using the names in table one as a reference
    – MH731Z
    Dec 16, 2018 at 1:34
  • You want to replace - i.e. to replace each word from your words list in each phrase. If you try to join phrases and words tables in SELECT query, you will obtain ALL matched pairs, but if you try to replace in UPDATE query using those pairs each prase will be updated only once, one word replacing will be performed only. Resume - you need to iterate over words list and to replace each word separately. It can be performed in stored procedure only.
    – Akina
    Dec 17, 2018 at 7:33

1 Answer 1

0

It won't be fast, but this may be part of what you need:

SELECT t2.*
    FROM t1
    JOIN t2  ON t2.email LIKE CONCAT('%', t1.name, '%')

That only identifies the rows that should be censored in the email, and only if the alignment of t1.name, etc, is good. That is, if t1.name is a substring of some other name, you could get some extra hits by this method.

Stick LIMIT 20 on that query and see what the output feels like.

Another approach might be:

FULLTEXT(email, text)  -- in t2

SELECT t2.* FROM t2 WHERE
    MATCH(email, text) AGAINST("all 16000 words from t1");

I have never seen such a big match-against, but perhaps it will work.

You could get a text copy of the list this way:

SET @@group_concat_max_len = 1234567;
SELECT GROUP_CONCAT(name SEPARATOR ' ') FROM t1;

and then copy the string of names into the above query.

2
  • thanks Rick, I will look into your suggestion, at the moment i am using c# to achieve this task but it takes about 1000 minutes to complete so hoping the mysql method would be quicker, I'll update you with the results
    – MH731Z
    Dec 16, 2018 at 21:27
  • @MH731Z - I will be surprised if MySQL is any faster at searching 8M rows for 16K strings. That's 128 billion actions. The FULLTEXT approach stands a change of being fewer "actions". There are other algorithms that might be useful in C# -- involving digraphs (maybe trigraphs) as a quick way to eliminate some of the 16K 'words'.
    – Rick James
    Dec 16, 2018 at 21:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.