Início / dba / Perguntas / 106535
Accepted
oNare
Asked: 2015-07-10 19:23:48 +0800 CST

Como dividir números e texto no MySQL

  • 772

Já procurei na internet procurando uma função como REGEXP_REPLACEno Oracle , regexp_replaceno PostgresSQL mas não encontrei uma parecida no MySQL apenas REGEXP e RLIKE , mas esses operadores apenas verificam se a string corresponde ao padrão contendo uma expressão regular e não qual é a correspondência .

Eu li sobre UDF como mysql-udf-regexp , mas não funciona para mim, pois quero algo que possa manipular stored functionspara tarefas de otimização mais rápidas ou quaisquer condições que eu queira.

Minhas perguntas são:

  • Tenho que fazer 2 funções se quiser deletar todas alpha charactersquando quiser só letras ou vice-versa?
  • Alguém conhece alguma maneira melhor de abordar isso?
mysql replace

2 respostas

  1. Eu tenho uma abordagem bastante feia que retirará caracteres alfanuméricos de uma variável de usuário

    ALFAS DESCASCANTES

    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;

    O ALFA DE REMOÇÃO FOI EXECUTADO

    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)

    RESULTADO DO STRIPPING ALPHAS

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

mysql>

    NUMÉRICOS DE REMOÇÃO

    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;

    NÚMEROS DE REMOÇÃO EXECUTADOS

    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)

    RESULTADO DOS NÚMEROS DE REMOÇÃO

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

mysql>

    REMOVENDO ALFANUMÉRICOS

    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;

    REMOÇÃO DE ALFANUMÉRICOS EXECUTADOS

    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)

    RETIRANDO RESULTADO ALFANUMÉRICO

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

mysql>

    EPÍLOGO

    Esta consulta SELECT destina-se a conter até 100 caracteres para remoção

    Você pode substituir (SELECT 'abcdefghijklmnopqrstuvwxyz0123456789' chars) L,por qualquer lista de caracteres que deseja remover de uma variável de usuário.

    DE UMA CHANCE !!!

    • 4
  2. Best Answer

    Obrigado a todos, mas vou usar esses FUCNTIONsque fiz. Um FUNCTIONpara extrair letras e outro para extrair números (mesmo decimais).

    Cartas:

    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 ;

    Numéricos/Dígitos:

    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

relate perguntas