REGEXP Issue
Good afternoon!
I am trying to run a regular expression to filter data that I want to put into a new table. I am running MySQL Workbench 8.0.4.41 CE on Windows 11. I have a table that contains a column named "code" that is set for longtext. The data in each row is a mix of numbers, letters and some special symbols (including " , : [ ] { } ) The ) at the end is not included. I have a regex that worked in notepad++ for searching, however I am sure that there are some differences with MySQL. The data is:
...","name":"name1","polarity":"AP_POWER","rarity":"RARE","..":...,"...":...,"...":...,"compatName":"compat1","type":"type1","description":["desc1"]},
(For simplicity sake I removed the data I don't want, indicated by ...)
The regex is:
(?<=name)(.*?)(?=\,)|(?=polarity")(.*?)(?=\,)|(?=rarity")(.*?)(?=\,)|(?=compatName")(.*?)(?=\,)|(?=type")(.*?)(?=\,)|(?=description")(.*?)(?=\})
Basically, what I am trying to do is to retrieve that data, and put it into a new table with the columns named name, polarity, rarity, compatName, type and description, with the values of each as the rows.
When I run the script, I get a syntax error. I am not very familiar with regex so I am sure there are mistakes or formatting issues. If there is a better way to do this, either with MySQL or something else, I am open to suggestions.
If anyone can help make this work I would appreciate it. Thanks!