3

I have searched over the internet looking for a function like REGEXP_REPLACE in Oracle, regexp_replace in PostgresSQL but I haven't find one similar in MySQL just REGEXP and RLIKE, but these operators just check whether the string matches pattern containing a regular expression not what the match is.

I have read about UDF like mysql-udf-regexp, but it doesn't work for me since I want something that I can manipulate like stored functions for faster optimization tasks or whatever conditions I want.

My questions are:

  • Do I have to make 2 functions if I want to delete all the alpha characters when I want just letters or vice versa?
  • Does anyone know any better way to approach this?
3

I have a rather ugly approach that will strip alphanumeric characters from a user variable

STRIPPING ALPHAS

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;

STRIPPING ALPHAS EXECUTED

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)

STRIPPING ALPHAS RESULT

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

mysql>

STRIPPING NUMERICS

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;

STRIPPING NUMERICS EXECUTED

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)

STRIPPING NUMERICS RESULT

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

mysql>

STRIPPING ALPHANUMERICS

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;

STRIPPING ALPHANUMERICS EXECUTED

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)

STRIPPING ALPHANUMERICS RESULT

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

mysql>

EPILOGUE

This SELECT query is meant to hold up to 100 characters for stripping

You can replace (SELECT 'abcdefghijklmnopqrstuvwxyz0123456789' chars) L, with any list of characters you wish to strip from a user variable.

GIVE IT A TRY !!!

3

Thanks everyone, but I'm going to use these FUCNTIONs that I made. One FUNCTION to extract letters and one to extract numbers (even decimals).

Letters:

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 ;

Numerics/Digits:

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 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.