主页 / dba / 问题 / 316440
Accepted
n34_panda
Asked: 2022-09-05 02:25:15 +0800 CST

Mysql如何帮助清理数据，特别是如果包含文本删除

  • 772

我有一个专栏：

尺寸
75cl
70im
7im
30cm
30爬升

我需要将文本转换为数字，但有条件：

尺寸
75cl
70
7
30
30cl

如果大小列包含“im”，那么我只想返回数字。

我试图写一些查询，如：

SELECT DISTINCT(LTRIM(RTRIM(size))) from tbl_size;

要确定我需要清理多少行，但我有点坚持有条件地显示行：

SELECT CASE WHEN size = '70im' THEN '70' etc

但是我有 3,000 个不同的值要适应，我对最好的方法有点坚持。
任何建议表示赞赏，我正在使用 MySQL。

mysql select

2 个回答

  1. Best Answer

    明智地做到这一点的唯一方法是使用正则表达式（正则表达式）！这是一个很棒的网站，他们有很好的快速入门和其他教程。这是 StackOverflow 的What does this regex mean“参考”（又名“规范”）问题页面 - 这是一个很棒的常见问题解答资源。

    编辑：答案完全改变了！

    下面的所有代码都可以在这里 找到。

    CREATE TABLE test
(
  measure VARCHAR (250) NOT NULL
);

    填充表格：

    INSERT INTO test VALUES
('75cl'),
('70im'),
('7im'),
('30cim'),
('30clim');

    然后运行以下代码：

    --
-- MySQL code
--

SELECT
  measure AS m,
  REGEXP_SUBSTR(measure, '^[0-9]+') AS num,
  REGEXP_SUBSTR(measure, '^[0-9]+cl|^[0-9]+') AS rex_str


--  LENGTH(CONCAT('xyz', REGEXP_SUBSTR(measure, '^[0-9]+'))) AS strlen,
 
--  (LENGTH(CONCAT('xyz', REGEXP_SUBSTR(measure, '^[0-9]+'))) - 2)
 
--  SUBSTR
--  (
--    (CONCAT('xyz', REGEXP_SUBSTR(measure, '^[0-9]+'))) FROM
--    (LENGTH(CONCAT('xyz', REGEXP_SUBSTR(measure, '^[0-9]+'))) - 2) FOR 2
--  )

FROM                                                                
  test;

    结果（留下一些额外的字段以显示思路 - 另外，请查看小提琴上的 PostgreSQL 代码）：

         m  num   rex_str
  75cl   75      75cl
  70im   70      70
   7im    7       7
 30cim   30      30
30clim   30      30cl

    正则表达式模式解释（'^[0-9]+cl|^[0-9]+'）：

    • ^- 指示行首的元字符

    • [0-9]- 方括号表示所谓的字符类 - 这将识别数字 0-9

    • +- 这意味着在正则表达式中出现一个或多个前面的项目 - 在这种情况下，数字

    • cl字符串的 - 对应于文字文本“cl” - 将挑选出cl并且仅cl- 不是im字符串的一部分

    • |- 最后，管道字符是正则表达式的意思OR- 所以我们可以根据需要链接不同的模式

    • 模式的后半部分拾取正常写入时不包含- 或厘升的字符串。cl因为我们只指定了数字，所以im字符串消失了

    如果您只有几个想要匹配的字符串（即cl, xx, yy...），这将很有效，但如果情况发生变化，您可能需要另一种策略。

    正则表达式是强大的工具，非常值得了解 - 它们很棘手并且可能非常复杂 - 请参阅有关电子邮件的这个问题。在功率/复杂性和效率之间进行权衡（查看这个6,900 个字符的怪物以匹配电子邮件）。

    您还应该意识到，使用 RDBMS 的内置函数而不是正则表达式实际上总是更好 - 它们很强大，但随着这种能力而来的是开销 - 你不应该忽视这一事实！有关潜在非技术问题的有力分析，请参见此处。

    • 2

  2. IF(size LIKE '%im', 0+size, size)似乎就足够了。

    这里有 3 个测试用例，为简单起见使用 SET 值。

    mysql> SET @size := '70cim';

mysql> SELECT IF(@size LIKE '%im', 0+@size, @size);
+--------------------------------------+
| IF(@size LIKE '%im', 0+@size, @size) |
+--------------------------------------+
| 70                                   |
+--------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '70cim' |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> SET @size := '123cl';

mysql> SELECT IF(@size LIKE '%im', 0+@size, @size);
+--------------------------------------+
| IF(@size LIKE '%im', 0+@size, @size) |
+--------------------------------------+
| 123cl                                |
+--------------------------------------+

mysql> SET @size := '987';

mysql> SELECT IF(@size LIKE '%im', 0+@size, @size);
+--------------------------------------+
| IF(@size LIKE '%im', 0+@size, @size) |
+--------------------------------------+
| 987                                  |
+--------------------------------------+

    如果它比简单地检查是否以“im”结尾更复杂，您可能需要一个CASE表达式而不是一个简单的IF函数。

    （我看到 -cim 和 -clim 的工作方式应该不同；请详细说明它们是如何区分的，并检查还有哪些其他奇怪的情况。）

    • 0

相关问题