Search code examples
mysqlregexp-replace

how can I delete this regex with mysql

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

Solution

  • 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)