how can I delete this regex with mysql-CodePudding

I have a UDF regex_replace which replace CASEINSENSITIVE a regex. Now I would like to get the string inside (and ) in this string. I did:

    SELECT regex_replace('[^0-9{MC.D}] ','',"HPLC Labor Öttl 1 Raumtemperatur(MC1.D.03.031)");

the output is, which is not desired: C1mmMC1.D.03.031

I would need. MC1.D.03.031 only. How can I do this with a regex suitable to my UDF?

thanks

CodePudding user response：

You could use SUBSTRING_INDEX https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_substring-index:

 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('HPLC Labor Öttl 1 Raumtemperatur(MC1.D.03.031)', '(', -1), ')', 1) ;

mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('HPLC Labor Öttl 1 Raumtemperatur(MC1.D.03.031)', '(', -1), ')', 1) ;
 ------------------------------------------------------------------------------------------------------ 
| SUBSTRING_INDEX(SUBSTRING_INDEX('HPLC Labor Öttl 1 Raumtemperatur(MC1.D.03.031)', '(', -1), ')', 1)  |
 ------------------------------------------------------------------------------------------------------ 
| MC1.D.03.031                                                                                         |
 ------------------------------------------------------------------------------------------------------ 
1 row in set (0.00 sec)