mysql - 从所有列的结尾和开头删除非字母数字字符

Question

我几乎没有超过 20 列的表，我必须

1. 先修剪
2. 删除每列末尾的所有非字母数字字符和非符号')'
3. 从每列的第一个位置删除所有非字母数字字符和非符号'('

如果它来自整个字符串，我可以简单地使用 replace 方法，但在我的例子中只能在第一个位置和最后一个位置。所以现在我正在使用子字符串，检查特殊字符并替换为空格。我觉得这是某种手动清洁，我敢肯定这不是一个优雅的清洁。

有什么可以帮助我清理数据的快速方法(查询)？

Answer 1

数据库必须在线吗？面对这个问题，我会考虑将数据转储到文件中，以使用 perl、awk 或其他适合以这种方式进行文本处理的工具进行处理。

如果那不可能，另一种选择是在 FUNCTION 中构造 munging 算法接收 VARCHAR，并将清理后的字符串作为 VARCHAR 返回(NB 未经测试的代码，仅用于解释):

CREATE FUNCTION cleanup(instr VARCHAR(255)) RETURNS VARCHAR(255));
    DECLARE outstr VARCHAR(255);
    SET outstr = TRIM(instr);
    IF NOT (outstr REGEXP '^[[:alnum:][.left-parenthesis.]]');
        SET outstr = SUBSTRING(outstr,2);
    END IF;
    WHILE NOT (outstr REGEXP '[[:alnum:][.right-parenthesis.]]$' DO
        SET outstr = LEFT(outstr, LENGTH(outstr)-1);
    END WHILE;
    SELECT outstr;
END FUNCTION;

然后您可以编写一个查询来读取系统目录，即 information_schema.columns 并生成所需的 UPDATE 语句。类似(未经测试)的内容:

SELECT CONCAT_WS(" ", "UPDATE", table_name, 
                 "SET", column_name, " = cleanup(", column_name, ")")
  FROM information_schema.columns
  WHERE table_schema = "your-database" AND collation_name IS NOT NULL

保存输出，检查并运行。

collat​​ion_name 过滤器应确保我们将其限制为仅文本类型的字段。同样，这是未经测试的，但应该给你一般的想法。您甚至可以使用 GROUP_CONCAT 构建一个版本，为每个表而不是每个列创建一个 SQL 语句，但这有点花哨。

显然，在运行任何将要执行如此广泛更新的内容之前，您会备份数据库...