主页 / dba / 问题 / 106535
Accepted
oNare
Asked: 2015-07-10 19:23:48 +0800 CST

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

  • 772

我在互联网上搜索了一个类似Oracle和PostgresSQLREGEXP_REPLACE的函数，但我在 MySQL 中没有找到类似的函数，只是REGEXP和RLIKE ，但是这些运算符只是检查字符串是否与包含正则表达式的模式匹配，而不是匹配是什么.regexp_replace

我已经阅读过类似mysql-udf-regexp之类的 UDF ，但它对我不起作用，因为我想要一些我可以操作的东西，比如stored functions更快的优化任务或我想要的任何条件。

我的问题是：

  • 如果我只想删除所有alpha characters字母或反之亦然，是否必须创建 2 个功能？
  • 有谁知道更好的方法来解决这个问题？
mysql replace

2 个回答

  1. 我有一个相当丑陋的方法，可以从用户变量中删除字母数字字符

    剥离 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,为您希望从用户变量中删除的任何字符列表。

    试试看 ！！！

    • 4
  2. Best Answer

    谢谢大家，但我将使用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 ;
    • 3

相关问题