Question 366746 in Mysql Question posted in
The official MySQL documentation can be found here.

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"
Tags:

2

Answers


  1. 0

    Instead of (wrong) escaping open parentheses, I put it inside a bracket as
    '.*/(d+)(?![^(]*[)])' .

    Or escape it by double backslash as follows:
    '.*/(d+)(?![^(]*\))'

    Login or Signup to reply.
  2. 0

    MySQL regex implementation does not support lookahead patterns, which is the (?...) construction. No regex with that mechanism will work in MySQL.

    Here’s a solution that works:

    SELECT REGEXP_REPLACE(REGEXP_REPLACE(myStr, '\([^)]*\)', ''), '.*/([[:digit:]]+).*', '$1') AS `result`
FROM myTable;

    Output given your example strings, tested on MySQL 8.3.0:

    +--------+
| result |
+--------+
| 13     |
| 1      |
| 1      |
| 8      |
| 9      |
| 2      |
+--------+

    Demo: https://dbfiddle.uk/s7oJvcj7

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search