Mysql如何幫助清理數據，特別是如果包含文本刪除

我有一個專欄：

我需要將文本轉換為數字，但有條件：

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

我試圖寫一些查詢，如：

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

要確定我需要清理多少行，但我有點堅持有條件地顯示行：

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

但是我有 3,000 個不同的值要適應，我對最好的方法有點堅持。

任何建議表示讚賞，我正在使用 MySQL。

明智地做到這一點的唯一方法是使用正則表達式（正則表達式）！這是一個很棒的網站，他們有很好的快速入門和其他教程。這是 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 的內置函式而不是正則表達式實際上總是更好 - 它們很強大，但隨著這種能力而來的是成本 - 你不應該忽視這一事實！有關潛在非技術問題的有力分析，請參見此處。

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 的工作方式應該不同；請詳細說明它們是如何區分的，並檢查還有哪些其他奇怪的情況。）

引用自：https://dba.stackexchange.com/questions/316440