What is the equivalent of REGEXP_SUBSTR in mysql?

Question

I want to extract a word from a string column of a table.

description
===========================
abc order_id: 2 xxxx yyy aa
mmm order_id: 3 nn kk yw

Expected result set

order_id
===========================
2
3

Table will at most have 100 rows, text length is ~256 char and column always has one order_id present. So performance is not an issue.

In Oracle, I can use REGEXP_SUBSTR for this problem. How would I solve this in MySQL?

Edit 1

I am using LOCATE and SUBSTR to solve the problem. The code is ugly. Ten minutes after writing the code, I am cursing the guy who wrote such an ugly code.

I didn't find the REGEXP_SUBSTR function in MySQL docs. But I am hoping that it exists..

Answer to : Why cant the table be optimized? Why is the data stored in such a dumb fashion?

The example I gave just denotes the problem I am trying to solve. In real scenario, I am using a DB based 3rd party queuing software for executing asynchronous tasks. The queue serializes the Ruby object as text. I have no control over the table structure OR the data format. The tasks in the queue can be recurring. In our test setup, some of the recurring tasks are failing because of stale data. I have to delete these tasks to prevent the error. Such errors are not common, hence I don't want to maintain a normalized shadow table.

Answer 1

or you can do this and save yourself the ugliness :

select SUBSTRING_INDEX(SUBSTRING_INDEX('habc order_id: 2 xxxx yyy aa',' ',3),' ',-1);