mysql - SQL 如果单元格中至少有两个定义的值，则替换该单元格中的值

Question

我有一个必须处理的列/变量。该列的单元格包含一些值，例如字母。如果单元格包含一组已定义字母中的至少两个字母(例如 abcd)，则所有这些特定字母都应替换为 M。

这是一个最小的例子。 string 是要处理的字段，结果应包含处理后的字符串:

-- Table
CREATE TABLE x0 (
string    VARCHAR(255)
, result  VARCHAR(255)
);

-- Values (after values is the desired result)
INSERT INTO x0(string) VALUES 
  ('--a---')         -- '--a--'
, ('-c')             -- '-c'
, ('a-d')            -- 'M-'
, ('b--cd')          -- 'M--'
, ('--c---d')        -- '--M---'
;

这是我的第一次尝试(仅考虑带有 a 的 string 以避免代码太长):

UPDATE x0 SET result = NULL;    
UPDATE x0
SET result = string
    , result = CASE WHEN string REGEXP 'a' AND string  NOT REGEXP '[bcd]' THEN result ELSE result END
    , result = CASE WHEN string REGEXP 'a' AND string  REGEXP '[bcd]' THEN REPLACE(result,'a','M') ELSE result END
    , result = CASE WHEN string REGEXP 'a' AND string  REGEXP '[bcd]' THEN REPLACE(result,'b','M') ELSE result  END 
    , result = CASE WHEN string REGEXP 'a' AND string  REGEXP '[bcd]' THEN REPLACE(result,'c','M') ELSE result  END
    , result = CASE WHEN string REGEXP 'a' AND string  REGEXP '[bcd]' THEN REPLACE(result,'d','M') ELSE result END
;
SELECT * FROM x0;

这是结果:

string  result
--a---  --a---
-c      -c
a-d     M-M
b--cd   b--cd
--c---d --c---d

我有两个问题需要解决:

1. 我的代码非常具体，我必须为每种可能的组合编写一个CASE WHEN。对于一般情况有什么聪明的方法吗？
2. 当我为每个 abcd 获取一个 M 时，如何删除除第一个之外的所有 M？

感谢您的帮助。

<小时/>

这是代码的长版本。想象一下该集合不仅包含 4 个字母，而且包含 15 个字母。

UPDATE x0
SET result = string
    , result = CASE WHEN string REGEXP 'a' AND string  NOT REGEXP '[bcd]' THEN result ELSE result END
    , result = CASE WHEN string REGEXP 'a' AND string      REGEXP '[bcd]' THEN REPLACE(result,'a','M') ELSE result END
    , result = CASE WHEN string REGEXP 'a' AND string      REGEXP '[bcd]' THEN REPLACE(result,'b','M') ELSE result  END 
    , result = CASE WHEN string REGEXP 'a' AND string      REGEXP '[bcd]' THEN REPLACE(result,'c','M') ELSE result  END
    , result = CASE WHEN string REGEXP 'a' AND string      REGEXP '[bcd]' THEN REPLACE(result,'d','M') ELSE result END

    , result = CASE WHEN string REGEXP 'b' AND string  NOT REGEXP '[acd]' THEN result ELSE result END
    , result = CASE WHEN string REGEXP 'b' AND string      REGEXP '[acd]' THEN REPLACE(result,'a','M') ELSE result END
    , result = CASE WHEN string REGEXP 'b' AND string      REGEXP '[acd]' THEN REPLACE(result,'b','M') ELSE result  END 
    , result = CASE WHEN string REGEXP 'b' AND string      REGEXP '[acd]' THEN REPLACE(result,'c','M') ELSE result  END
    , result = CASE WHEN string REGEXP 'b' AND string      REGEXP '[acd]' THEN REPLACE(result,'d','M') ELSE result END

    , result = CASE WHEN string REGEXP 'c' AND string  NOT REGEXP '[abd]' THEN result ELSE result END
    , result = CASE WHEN string REGEXP 'c' AND string      REGEXP '[abd]' THEN REPLACE(result,'a','M') ELSE result END
    , result = CASE WHEN string REGEXP 'c' AND string      REGEXP '[abd]' THEN REPLACE(result,'b','M') ELSE result  END 
    , result = CASE WHEN string REGEXP 'c' AND string      REGEXP '[abd]' THEN REPLACE(result,'c','M') ELSE result  END
    , result = CASE WHEN string REGEXP 'c' AND string      REGEXP '[abd]' THEN REPLACE(result,'d','M') ELSE result END

    , result = CASE WHEN string REGEXP 'd' AND string  NOT REGEXP '[abc]' THEN result ELSE result END
    , result = CASE WHEN string REGEXP 'd' AND string      REGEXP '[abc]' THEN REPLACE(result,'a','M') ELSE result END
    , result = CASE WHEN string REGEXP 'd' AND string      REGEXP '[abc]' THEN REPLACE(result,'b','M') ELSE result  END 
    , result = CASE WHEN string REGEXP 'd' AND string      REGEXP '[abc]' THEN REPLACE(result,'c','M') ELSE result  END
    , result = CASE WHEN string REGEXP 'd' AND string      REGEXP '[abc]' THEN REPLACE(result,'d','M') ELSE result END

;
SELECT * FROM x0;

string  result
--a---  --a---
-c  -c
a-d     M-M
b--cd   M--MM
--c---d --M---M

Answer 1

我找到了一个可能的答案，但仍然不完美。首先，我向每个定义的字符添加一个 M。然后我数一下M，如果超过 1 个则删除 a、b、c、d。

UPDATE x0 SET result = NULL;
UPDATE x0
SET result = string
    , result = REPLACE(result,'a','Ma')
    , result = REPLACE(result,'b','Mb')
    , result = REPLACE(result,'c','Mc')
    , result = REPLACE(result,'d','Md')
, result = CASE 
           WHEN CHAR_LENGTH(result) - CHAR_LENGTH(REPLACE(result,'M','')) > 1 
       THEN REPLACE(REPLACE(REPLACE(REPLACE(result,'a',''),'b',''),'c',''),'d','')
       ELSE REPLACE(result,'M','') END
    , result = CONCAT(SUBSTR(result,1,POSITION('M' IN result)),REPLACE(SUBSTR(result,POSITION('M' IN result)+1),'M',''))

;
SELECT * FROM x0;


string  result
--a---  --a---
-c      -c
a-d     M-
b--cd   M--
--c---d --M---

MariaDB版本> = 10还具有函数REGEXP_REPLACE(subject,pattern,replace)可以解决此类问题。