How to find if words are repeated twice in a row

Question

For example, I have the following paragraph in a MySQL database table field

"Turning off the illumination of an object of an astronomical object, as in an eclipse of the moon when the earth is between"

Here the word " object " appears twice in a row (next to each other).

I want to check if this case appears in other records in the same table. Is there an easy way to do this with PHP and MySQL

Answer 1

if (preg_match('/\\b(\\w+)\\s\\1\\b/', $subject)) { //has repetition } 

Answer 2

This regex works: (\b\w+\b)(?=\s?\1) (see example )

To make it work in MySQL, Word ( \b ) borders must be replaced with MySQL equivalents [[:<:]] and [[:>:]] and \w with [[:alnum:]] :

 SELECT * FROM your_table WHERE your_field REGEXP '([[:<:]][[:alnum:]]+[[:>:]])(?=\\s?\\1)'; 

But this query gives an error because, as Matt said, MySQL does not support backlinks . Therefore, this is not possible with the current MySQL REGEXP.

Answer 3

You can use the following regular expression for your purposes:

 (\b\w+)(?:\s+\1)+ 

or

 \b(\w+)(?:\s+\1)+ 

This will match any number of repeating words with any number of spaces between words.

Please note that depending on how the MySQL or PHP regular expression engine works (depending on which one you are using), you may need to escape backslashes (and possibly brackets), and you will need to use any delimiters that the regex engine uses for patterns. (Although if MySQL does not allow backlinks as Matt states, then you just need to use PHP to do this.)

EDIT: Updated with word boundary check; I didn’t understand that \w does not automatically check word boundaries automatically for preg patterns.

Answer 4

SELECT * FROM table WHERE REGEXP field 'search term'

Will return strings containing the corresponding term.

Answer 5

What about

 SELECT * FROM yourtable WHERE yourfield LIKE "%yourword%%yourword%" 

in my quick test on localhost, it worked.