Mysql

如何在 MySQL 中拆分數字和文本

  • August 15, 2015

我在網際網路上搜尋了一個類似Oracle和PostgresSQLREGEXP_REPLACE的函式，但我沒有在 MySQL 中找到類似的函式，只是REGEXP和RLIKE **，**但是這些運算符只是檢查字元串是否與包含正則表達式的模式匹配，而不是匹配是什麼.regexp_replace

我已經閱讀過類似mysql-udf-regexp之類的 UDF ，但它對我不起作用，因為我想要一些我可以操作的東西，比如stored functions更快的優化任務或我想要的任何條件。

我的問題是：

  • 如果我只想刪除所有alpha characters字母或反之亦然，是否必須創建 2 個功能？
  • 有誰知道更好的方法來解決這個問題？

謝謝大家，但我將使用FUCNTIONs我製作的這些。一種FUNCTION用於提取字母，一種用於提取數字（甚至是小數）。

字母：

USE `test`;
DROP function IF EXISTS `alpha`;

DELIMITER $$
USE `test`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `alpha`(
vStr CHAR(75)) RETURNS char(32) CHARSET latin1
BEGIN 
   DECLARE vInd, vLen SMALLINT DEFAULT 1; 
   DECLARE vReturn CHAR(32) DEFAULT ''; 
   DECLARE vCharacter CHAR(1); 
   REPEAT 
   SET vLen = CHAR_LENGTH(vStr); 
   BEGIN 
       SET vCharacter = MID(vStr,vInd,1); 
       IF vCharacter REGEXP '[[:alpha:]]' THEN 
           SET vReturn=CONCAT(vReturn,vCharacter); 
       END IF; 
       SET vInd =vInd+1; 
   END; 
   UNTIL vInd > vLen END REPEAT; 
   RETURN vReturn; 
END$$

DELIMITER ;

數字/數字：

USE `test`;
DROP function IF EXISTS `test`.`num`;

DELIMITER $$
USE `test`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `num`(
vStr CHAR(75)) RETURNS char(32) CHARSET latin1
BEGIN 
   DECLARE vInd, vLen SMALLINT DEFAULT 1; 
   DECLARE vReturn CHAR(32) DEFAULT ''; 
   DECLARE vCharacter CHAR(1); 
   REPEAT 
       SET vLen = CHAR_LENGTH(vStr); 
   BEGIN 
       SET vCharacter = MID(vStr,vInd,1); 
       IF vCharacter REGEXP '[[:digit:]|[:punct:]]' THEN 
           SET vReturn=CONCAT(vReturn,vCharacter); 
       END IF; 
       SET vInd =vInd+1; 
   END; 
   UNTIL vInd > vLen END REPEAT; 
   RETURN CAST(vReturn AS UNSIGNED); 
END$$

DELIMITER ;

我有一個相當醜陋的方法，可以從使用者變數中刪除字母數字字元

剝離 Alpha

SET @st='r1+o2l-3a4*n5d6o7';
SELECT (@st:=REPLACE(@st,ch,'')) FROM
(
   SELECT (x*10+y) ndx,SUBSTR(chars,x*10+y,1) ch FROM
   (SELECT 'abcdefghijklmnopqrstuvwxyz' chars) L,
   (SELECT 0 x UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
   UNION SELECT 5 x UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) X,
   (SELECT 0 y UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
   UNION SELECT 5 x UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) Y
   WHERE SUBSTR(chars,x*10+Y,1)<>'' ORDER BY SUBSTR(chars,x*10+y,1)
) alpha;
SELECT @st;

剝離 Alpha 已執行

mysql> SELECT (@st:=REPLACE(@st,ch,'')) FROM
   -> (
   ->     SELECT (x*10+y) ndx,SUBSTR(chars,x*10+y,1) ch FROM
   ->     (SELECT 'abcdefghijklmnopqrstuvwxyz' chars) L,
   ->     (SELECT 0 x UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
   ->     UNION SELECT 5 x UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) X
   ->     (SELECT 0 y UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
   ->     UNION SELECT 5 x UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) Y
   ->     WHERE SUBSTR(chars,x*10+Y,1)<>'' ORDER BY SUBSTR(chars,x*10+y,1)
   -> ) alpha;
+---------------------------+
| (@st:=REPLACE(@st,ch,'')) |
+---------------------------+
| r1+o2l-34*n5d6o7          |
| r1+o2l-34*n5d6o7          |
| r1+o2l-34*n5d6o7          |
| r1+o2l-34*n56o7           |
| r1+o2l-34*n56o7           |
| r1+o2l-34*n56o7           |
| r1+o2l-34*n56o7           |
| r1+o2l-34*n56o7           |
| r1+o2l-34*n56o7           |
| r1+o2l-34*n56o7           |
| r1+o2l-34*n56o7           |
| r1+o2-34*n56o7            |
| r1+o2-34*n56o7            |
| r1+o2-34*56o7             |
| r1+2-34*567               |
| r1+2-34*567               |
| r1+2-34*567               |
| 1+2-34*567                |
| 1+2-34*567                |
| 1+2-34*567                |
| 1+2-34*567                |
| 1+2-34*567                |
| 1+2-34*567                |
| 1+2-34*567                |
| 1+2-34*567                |
| 1+2-34*567                |
+---------------------------+
26 rows in set (0.00 sec)

剝離 Alpha 結果

mysql> SELECT @st;
+------------+
| @st        |
+------------+
| 1+2-34*567 |
+------------+
1 row in set (0.00 sec)

mysql>

剝離數字

SET @st='r1+o2l-3a4*n5d6o7';
SELECT (@st:=REPLACE(@st,ch,'')) FROM
(
   SELECT x+1 ndx,SUBSTR(chars,x+1,1) ch FROM
   (SELECT '0123456789' chars) L,
   (SELECT 0 x UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
   UNION SELECT 5 x UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) X
) numerics;
SELECT @st;

剝離數字執行

mysql> SET @st='r1+o2l-3a4*n5d6o7';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT (@st:=REPLACE(@st,ch,'')) FROM
   -> (
   ->     SELECT x+1 ndx,SUBSTR(chars,x+1,1) ch FROM
   ->     (SELECT '0123456789' chars) L,
   ->     (SELECT 0 x UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
   ->     UNION SELECT 5 x UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) X
   -> ) numerics;
+---------------------------+
| (@st:=REPLACE(@st,ch,'')) |
+---------------------------+
| r1+o2l-3a4*n5d6o7         |
| r+o2l-3a4*n5d6o7          |
| r+ol-3a4*n5d6o7           |
| r+ol-a4*n5d6o7            |
| r+ol-a*n5d6o7             |
| r+ol-a*nd6o7              |
| r+ol-a*ndo7               |
| r+ol-a*ndo                |
| r+ol-a*ndo                |
| r+ol-a*ndo                |
+---------------------------+
10 rows in set (0.00 sec)

剝離數字結果

mysql> SELECT @st;
+------------+
| @st        |
+------------+
| r+ol-a*ndo |
+------------+
1 row in set (0.00 sec)

mysql>

剝離字母數字

SET @st='r1+o2l-3a4*n5d6o7';
SELECT (@st:=REPLACE(@st,ch,'')) FROM
(
   SELECT (x*10+y) ndx,SUBSTR(chars,x*10+y,1) ch FROM
   (SELECT 'abcdefghijklmnopqrstuvwxyz0123456789' chars) L,
   (SELECT 0 x UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
   UNION SELECT 5 x UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) X,
   (SELECT 0 y UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
   UNION SELECT 5 x UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) Y
   WHERE SUBSTR(chars,x*10+Y,1)<>'' ORDER BY SUBSTR(chars,x*10+y,1)
) alphanumeric;
SELECT @st;

剝離字母數字已執行

mysql> SET @st='r1+o2l-3a4*n5d6o7';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT (@st:=REPLACE(@st,ch,'')) FROM
   -> (
   ->     SELECT (x*10+y) ndx,SUBSTR(chars,x*10+y,1) ch FROM
   ->     (SELECT 'abcdefghijklmnopqrstuvwxyz0123456789' chars) L,
   ->     (SELECT 0 x UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
   ->     UNION SELECT 5 x UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) X,
   ->     (SELECT 0 y UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
   ->     UNION SELECT 5 x UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) Y
   ->     WHERE SUBSTR(chars,x*10+Y,1)<>'' ORDER BY SUBSTR(chars,x*10+y,1)
   -> ) alphanumeric;
+---------------------------+
| (@st:=REPLACE(@st,ch,'')) |
+---------------------------+
| r1+o2l-3a4*n5d6o7         |
| r+o2l-3a4*n5d6o7          |
| r+ol-3a4*n5d6o7           |
| r+ol-a4*n5d6o7            |
| r+ol-a*n5d6o7             |
| r+ol-a*nd6o7              |
| r+ol-a*ndo7               |
| r+ol-a*ndo                |
| r+ol-a*ndo                |
| r+ol-a*ndo                |
| r+ol-*ndo                 |
| r+ol-*ndo                 |
| r+ol-*ndo                 |
| r+ol-*no                  |
| r+ol-*no                  |
| r+ol-*no                  |
| r+ol-*no                  |
| r+ol-*no                  |
| r+ol-*no                  |
| r+ol-*no                  |
| r+ol-*no                  |
| r+o-*no                   |
| r+o-*no                   |
| r+o-*o                    |
| r+-*                      |
| r+-*                      |
| r+-*                      |
| +-*                       |
| +-*                       |
| +-*                       |
| +-*                       |
| +-*                       |
| +-*                       |
| +-*                       |
| +-*                       |
| +-*                       |
+---------------------------+
36 rows in set (0.00 sec)

剝離字母數字結果

mysql> SELECT @st;
+------+
| @st  |
+------+
| +-*  |
+------+
1 row in set (0.00 sec)

mysql>

結語

此 SELECT 查詢最多可容納 100 個字元以進行剝離

您可以替換(SELECT 'abcdefghijklmnopqrstuvwxyz0123456789' chars) L,為您希望從使用者變數中刪除的任何字元列表。

試一試 ！！！

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

相關問答

Mysql

使用 SQL 重命名表中所有出現的 HTML 類名

  • June 16, 2021
檢視問答
Mysql

MySQL 從字元串中間刪除引號，同時保持字元串的其餘部分完好無損

  • August 24, 2022
檢視問答
Mysql

用另一個表中的數據替換 MYSQL 表 - 無需停機

  • December 9, 2021
檢視問答
Mysql

PolyLang WP 更新以將一個字元串替換為另一個

  • March 22, 2021
檢視問答
Mysql

如何排除從 mysqldumpslow 命令返回的查詢

  • February 19, 2021
檢視問答
Mysql

如何從單個列中提取證書屬性並拆分為多個？

  • October 24, 2020
檢視問答