Delete the duplicate words from array of strings in MySQL

serge Published at Dev

Serge

I have table with 2 varchar columns - col_name1 and col_name2

(1, 'hello world', 'hello test'),
(2, 'the stack over', 'over the flow'),
(3, 'hello from my sql fiddle', 'hello my sql');

See SQLFIDDLE http://sqlfiddle.com/#!9/cf90c1/1

I'm looking for a way to find the duplicate words in two columns and DELETE such the words from col_name1.

It means after a Mysql operation + UPDATE + SET - col_name1 should contain the words like below

(1, 'world', 'hello test'),
(2, 'stack', 'over the flow'),
(3, 'from fiddle', 'hello my sql');

user8406805

Here is the solution to your problem:

The SQL that resolve your problem statement:

update table_name x3
join (
select id,replace(group_concat(w),',',' ') w from (SELECT id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.c, ' ', x.x), ' ', -1) w
FROM (SELECT id,concat(col_name1) c FROM table_name) t
INNER JOIN
(
    SELECT 1 + a.i + b.i * 10 x
    FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
) x
ON (LENGTH(t.c) +1 - LENGTH(REPLACE(t.c, ' ', ''))) >= x.x
group by id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.c, ' ', x.x), ' ', -1)) x2
where not exists (select 1 from (SELECT id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.c, ' ', x.x), ' ', -1) w
FROM (SELECT id,concat(col_name2,' ',col_name1) c FROM table_name) t
INNER JOIN
(
    SELECT 1 + a.i + b.i * 10 x
    FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
) x
ON (LENGTH(t.c) +1 - LENGTH(REPLACE(t.c, ' ', ''))) >= x.x
group by id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.c, ' ', x.x), ' ', -1) 
having count(1) > 1) x1 where x2.id = x1.id and x2.w = x1.w)
group by id
) x
on x3.id = x.id
set x3.col_name1 = x.w;

Below is the Example from sample data to expected output:

mysql> create table table_name(id int, col_name1 varchar(200),col_name2 varchar(200));
Query OK, 0 rows affected (0.36 sec)

mysql> insert into table_name values
    -> (1, 'hello world', 'hello test'),
    -> (2, 'the stack over', 'over the flow'),
    -> (3, 'hello from my sql fiddle', 'hello my sql');
Query OK, 3 rows affected (0.11 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> update table_name x3
    -> join (
    -> select id,replace(group_concat(w),',',' ') w from (SELECT id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.c, ' ', x.x), ' ', -1) w
    -> FROM (SELECT id,concat(col_name1) c FROM table_name) t
    -> INNER JOIN
    -> (
    ->     SELECT 1 + a.i + b.i * 10 x
    ->     FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
    ->     CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
    -> ) x
    -> ON (LENGTH(t.c) +1 - LENGTH(REPLACE(t.c, ' ', ''))) >= x.x
    -> group by id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.c, ' ', x.x), ' ', -1)) x2
    -> where not exists (select 1 from (SELECT id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.c, ' ', x.x), ' ', -1) w
    -> FROM (SELECT id,concat(col_name2,' ',col_name1) c FROM table_name) t
    -> INNER JOIN
    -> (
    ->     SELECT 1 + a.i + b.i * 10 x
    ->     FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
    ->     CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
    -> ) x
    -> ON (LENGTH(t.c) +1 - LENGTH(REPLACE(t.c, ' ', ''))) >= x.x
    -> group by id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.c, ' ', x.x), ' ', -1) 
    -> having count(1) > 1) x1 where x2.id = x1.id and x2.w = x1.w)
    -> group by id
    -> ) x
    -> on x3.id = x.id
    -> set x3.col_name1 = x.w;
Query OK, 3 rows affected (0.13 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from table_name;
+------+-------------+---------------+
| id   | col_name1   | col_name2     |
+------+-------------+---------------+
|    1 | world       | hello test    |
|    2 | stack       | over the flow |
|    3 | from fiddle | hello my sql  |
+------+-------------+---------------+
3 rows in set (0.00 sec)

Hope, It will resolve your problem. All the best!!!

EDIT - as per request of question owner: To handle a large amount of words. Now It will handle the words upto 10000

update table_name x3
join (
select id,replace(group_concat(w),',',' ') w from (SELECT id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.c, ' ', x.x), ' ', -1) w
FROM (SELECT id,concat(col_name1) c FROM table_name) t
INNER JOIN
(
    SELECT 1 + a.i + b.i * 10 x
    FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d 
) x
ON (LENGTH(t.c) +1 - LENGTH(REPLACE(t.c, ' ', ''))) >= x.x
group by id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.c, ' ', x.x), ' ', -1)) x2
where not exists (select 1 from (SELECT id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.c, ' ', x.x), ' ', -1) w
FROM (SELECT id,concat(col_name2,' ',col_name1) c FROM table_name) t
INNER JOIN
(
    SELECT 1 + a.i + b.i * 10 x
    FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d 
) x
ON (LENGTH(t.c) +1 - LENGTH(REPLACE(t.c, ' ', ''))) >= x.x
group by id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.c, ' ', x.x), ' ', -1) 
having count(1) > 1) x1 where x2.id = x1.id and x2.w = x1.w)
group by id
) x
on x3.id = x.id
set x3.col_name1 = x.w;

Collected from the Internet

Please contact [email protected] to delete if infringement.