Regular Expression convert to MYSQL format

I have a Regular Expression .*\/(\d+)(?![^(]*\)) that it works in other program e.g., C#. And I want to use it in MYSQL, and it return error

SELECT REGEXP_SUBSTR(myStr, '.*\/(\d+)(?![^(]*\))'), c.* FROM mytable c;
/* SQL Error (1139): Regex error 'unmatched closing parenthesis at offset 16' */

The Regular Expression actually will return result like below. It get the number next to last "/" that not inside brackets

e.g.,
string: "/13/A01(VE)(S)", expected result: "13"
string: "(2)/1(D/3G)", expected result: "1"
string: "/S03(RED/09)/1/(TN)", expected result: "1"
string: "/2(V)/8", expected result: "8"
string: "/BR03(JTF07)(CE)(W)/9", expected result: "9"
string: "(NT)/2(U/V)(J)", expected result: "2"

Answers

In MySQL, the REGEXP_SUBSTR function does not support lookahead assertions like (?![^(]*\)) in your regular expression. However, you can achieve the same result using other string manipulation functions available in MySQL.

Here's an alternative approach to extract the desired substring:

SELECT 
    SUBSTRING_INDEX(SUBSTRING_INDEX(myStr, '/', -2), '(', 1) AS result,
    c.*
FROM 
    mytable c;

Explanation:

1. SUBSTRING_INDEX(myStr, '/', -2): This extracts the substring after the last '/'.
2. SUBSTRING_INDEX(result_from_step_1, '(', 1): This further extracts the substring before the first '(' from the result obtained in step 1.

This query will return the expected result based on your examples.