How to split numbers and text in MySQL

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?

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 (x10+y) ndx,SUBSTR(chars,x10+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 U

StackExchange Database Administrators Q#106535, answer score: 4