CODEWITHSUNDEEP
duplicatesmariadb
danone
danone

Reputation: 157

mysql statement remove repeated words in string

I would like to be able to use sql statement which allow me to remove record if there is repeated words in string column "title". There is several repeated word in title e.g "cervical cancer survival rate in the world cancer survivals" I would like to search and remove this field because word "cancer" repeated. Note that there could be another word that I don't know repeated so it is not only cancer word that I am looking for.

table structure
table name hospital
ID Varchar(32) utf8_general_ci
Title Varchar(300) utf8_general_ci
Summary Varchar(300) utf8_general_ci

Upvotes: 0

Views: 501

Answers (1)

Rick James
Rick James

Reputation: 142296

(This applies to MariaDB, not MySQL.)

This will match a 'word':

[[:<:]]\w+[[:>:]]

Since you have MariaDB, this backreference is available:

\1

So, this should match a title with a duplicated word:

title REGEXP [[:<:]](\w+)[[:>:]].*[[:<:]]\1[[:>:]]

(Depending on the context, you may need to double up the backslash.)

Reference: https://mariadb.com/kb/en/library/pcre/
See also REGEXP_REPLACE(): https://mariadb.com/kb/en/library/regexp_replace/

Upvotes: 1

Related Questions