Re: REGEXP_SUBSTR function missing

[Jon Stephens]

Sounds to me like an incomplete or failed attempt to upgrade to an 8.0 version.

The mysql.func table is for *user-defined* functions only.

This is from a fresh 8.0 installation from source into a clean installation directory:

# Example copied verbatim from 
# https://dev.mysql.com/doc/refman/8.0/en/regexp.html#function_regexp-substr 

mysql> SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+', 1, 3);
+----------------------------------------------+
| REGEXP_SUBSTR('abc def ghi', '[a-z]+', 1, 3) |
+----------------------------------------------+
| ghi                                          |
+----------------------------------------------+
1 row in set (0.00 sec)

# Present in mysql.func?

mysql> SELECT * FROM mysql.func;
Empty set (0.00 sec)

# What does SHOW CREATE FUNCTION show?

mysql> SHOW CREATE FUNCTION REGEXP_SUBSTR;
ERROR 1305 (42000): FUNCTION REGEXP_SUBSTR does not exist

# What about SHOW FUNCTION?

mysql> SHOW FUNCTION STATUS LIKE '%REGEX%';
Empty set (0.00 sec)

# Found in I_S.ROUTINES?

mysql> SELECT * FROM INFORMATION_SCHEMA.ROUTINES 
    -> WHERE ROUTINE_NAME LIKE '%REGEX%';
Empty set (0.02 sec)

# MySQL version

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.29    |
+-----------+
1 row in set (0.00 sec)

I'm not sure that I'd run your MySQL server as it is now in production.


cheers

Jon Stephens
MySQL Documentation Team @ Oracle

MySQL Dev Zone
MySQL Server Documentation
Oracle