How to find if there are words repeated twice in a row

Question

For example i have the below paragraph in a MySQL database table field

"The cutting off of the illumination of an astronomical object object, as in an eclipse of the Moon, when the Earth comes 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 same table. Is there an easy way to do this using PHP and MySQL

Answer 1

SELECT * FROM table WHERE field REGEXP 'search term'

Will return rows that contain the appropriate term.

Answer 2

You're going to have to use regex. I've never used regex in MySQL, but this is how you could do it in JavaScript:

'bla bla here are are some words'.replace(/\b(\w+)\s*\1/g, '$1');
=> output: "bla here are some words"

So I suppose the query would be:

select * from yourTable where paragraph rlike '\\b(\\w+)\\s*\\1'

This assumes MySQL supports backreferences.

Edit:

It appears MySQL does not support backreferences. So your task is basically impossible with MySQL. You'd have to load all of the paragraphs into PHP (or similar) and run the regex there..

Answer 3

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

Answer 4

what about

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

in my fast testcase on localhost, it worked.

Answer 5

You can use the following regex for your purposes:

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

or

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

This will match any number of repeated words, with any amount of whitespace between words.

Do note that, depending on how the MySQL or PHP regex engine works (whichever one you use), the backslashes (and possibly parentheses) may need to be escaped, and you'll need to use whatever delimiters the regex engine uses for patterns. (Though if MySQL does not permit backreferences as Matt states, then you'll just have to use PHP for that.)

EDIT: Updated with word boundary check; didn't realize that \w doesn't do word boundary checking automatically for preg patterns.

Answer 6

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

To make it work in MySQL, the Word Boundaries (\b) must be replaced with MySQL equivalent [[:<:]] 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 backreferences. So, it's not possible with the current MySQL REGEXP.