About mysql regex,how do I search and return string use mysql regex

My table filed's value is "<script type="text/javascript"src="http://localhost:8080/db/widget/10217EN/F">&l开发者_开发问答t;/script>",

I want to analyse this string and fetch the id 10217,how to do use mysql regex? I know python regex group function can return the id 10217,but i'm not familiar with mysql regex.

Please help me,Thank you very much.

---

MySQL regular expressions do not support subpattern extraction. You will probably have better luck iterating over all of the rows in your database and storing the results in a new column.

---

As far as I know, you can't use MySQL's REGEXP for substring retrieval; it is designed for use in WHERE clauses and is limited to returning 0 or 1 to indicate failure or success at a match.

Since your pattern is pretty well defined, you can probably retrieve the id with a query that uses SUBSTR and LOCATE. It will be a bit of a mess since SUBSTR wants the start index and the length of the substring (it would be easier if it took the end index). Perhaps you could use TRIM to chop off the unwanted trailing part.

---

This query get the Id from the field

SELECT substring_index(SUBSTRING_INDEX(testvar,'/',-3),'EN',1) from testtab;

where as testtab - is table name , testvar - is field name

inner substring get string starts with last 3 / which is

mysql> SELECT SUBSTRING_INDEX(testvar,'/',-3) from testtab;

+----------------------------+
| SUBSTRING_INDEX(testvar,'/',-3) |
+----------------------------+
| 10217EN/F"> |
| 10222EN/F"> |
+----------------------------+
2 rows in set (0.00 sec)

outer substring get

mysql> SELECT substring_index(SUBSTRING_INDEX(testvar,'/',-3),'EN',1) from testtab;

+----------------------------------------------------+
| substring_index(SUBSTRING_INDEX(testvar,'/',-3),'EN',1)
| +----------------------------------------------------+
| 10217 |
| 10222 |
+----------------------------------------------------+
2 rows in set (0.00 sec)