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

Improve this question
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 '18 at 23:51
  • 1
    Are these "words" alone in some column? Or buried in a long text column? – Rick James Dec 16 '18 at 0:30
  • you have words in columns?? or have full text of CLOB or what?? – nikhil sugandh Dec 16 '18 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 '18 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 '18 at 7:33
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.

Improve this answer
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 '18 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 '18 at 21:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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