MariaDB – Remove repeated words in string

測試環境為 CentOS 8 x86_64 虛擬機.

欄位資料為 ‘ABC12 , C2CC , ABC12 , C2CC , DD23’ 這樣有辦法移除重覆出現在單字嗎? 目前看到一個方式 – https://stackoverflow.com/questions/50225240/mysql-statement-remove-repeated-words-in-string

利用標準表示式的語法 (請參考 – https://benjr.tw/102758 ) 來處理,以下語法代表一個單字 如 : ABC

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

再加上 \1 代表前面 () 內的字串.

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

實際使用上須把 \w 取代成 \\w 與 \1 取代成 \\1 .

[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 30
Server version: 10.3.28-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

下面範例可以看到該標準標示式的確移除了重覆出現的單字.

MariaDB [(none)]> SELECT REGEXP_REPLACE('ABC12 , C2CC , ABC12 , C2CC , DD23' , '[[:<:]](\\w+)[[:>:]].*[[:<:]]\\1[[:>:]]' , '\\1') AS Test ;
+---------------------+
| Test                |
+---------------------+
| ABC12 , C2CC , DD23 |
+---------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> SELECT REGEXP_REPLACE('ABC12 C2CC ABC12 C2CC DD23' , '[[:<:]](\\w+)[[:>:]].*[[:<:]]\\1[[:>:]]' , '\\1') AS Test ;
+-----------------+
| Test            |
+-----------------+
| ABC12 C2CC DD23 |
+-----------------+
1 row in set (0.000 sec)

但一但重覆出現的單字再重覆就似乎無法處理了.

MariaDB [(none)]> SELECT REGEXP_REPLACE('ABC12 , C2CC , ABC12 , C2CC , DD23 , DD23 , ABC12' , '[[:<:]](\\w+)[[:>:]].*[[:<:]]\\1[[:>:]]' , '\\1') AS Test ;
+-------+
| Test  |
+-------+
| ABC12 |
+-------+
1 row in set (0.000 sec)