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

Question

我有几个表格，每个表格都有超过20列。我需要：

1. 首先去除空格
2. 去除每一列结尾处的所有非字母数字字符和非“)”符号
3. 去除每一列开头处的所有非字母数字字符和非“（”符号

如果是针对整个字符串，可以使用replace方法，但在我的情况下，只需在第一个位置和最后一个位置进行操作。因此，我现在使用子字符串，检查特殊字符并用空格替换。我感觉这种方法很繁琐，肯定不够优雅。

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

- Bujji

---

这些数据长度是否是一致的？这些不需要的字符是否总是存在？ - JYelton

谢谢JYelton，我很抱歉我应该也写下那个。不，它们并不总是存在，而且数据长度也不相同。 - Bujji

Answer 1

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

如果不可能的话，另一个选择是在一个函数内构建混合算法，该函数接收VARCHAR，并返回清理后的字符串作为VARCHAR（注意：未经测试的代码，仅用于说明）：

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

保存输出，检查并运行。

collation_name 过滤器应确保我们仅限于文本类型字段。同样，这未经测试，但应该给您一个大致的想法。您甚至可以使用 GROUP_CONCAT 来构建一个版本，它会为每个表创建一个单独的 SQL 语句，而不是每个列，但那有点花哨。

显然，在运行任何执行如此广泛的更新的操作之前，您应该备份数据库...

- RET

---

非常感谢 RET，我将进行测试。 - Bujji

它正在工作@RET，只是清理部分有一些语法错误。我已经纠正并更新了它...再次非常感谢您提供的快速解决方案和精彩解决方案。 - Bujji

很高兴听到你解决了这个问题。我目前没有访问 mySQL 服务器的权限，因此在发布之前无法进行适当的测试。 - RET