What Is The Equivalent Of Regexp_substr In Mysql?

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

Solution 1:

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

Yes, starting from MySQL 8.0 it is supported. Regular Expressions:

REGEXP_SUBSTR(expr, pat[, pos[, occurrence[, match_type]]])

Returns the substring of the string expr that matches the regular expression specified by the pattern pat, NULL if there is no match. If expr or pat is NULL, the return value is NULL.

Solution 2:

Like Konerak said, there is no equivalent of REGEXP_SUBSTR in MySql. You could do what you need using SUBSTRING logic, but it is ugly :

SELECTSUBSTRING(lastPart.end, 1, LOCATE(' ', lastPart.end) -1) AS orderId
FROM
  (
    SELECTSUBSTRING(dataset.description, LOCATE('order_id: ', dataset.description) + LENGTH('order_id: ')) ASendFROM
      (
        SELECT'abc order_id: 2 xxxx yyy aa'AS description
        UNIONSELECT'mmm order_id: 3 nn kk yw'AS description
        UNIONSELECT'mmm order_id: 1523 nn kk yw'AS description
      ) AS dataset
    ) AS lastPart

---

Edit: You could try this user defined function providing access to perl regex in MySql

SELECT 
  PREG_CAPTURE( '/.*order_id:\s(\d+).*/', dataset.description,1)
FROM
  (
    SELECT'abc order_id: 2 xxxx yyy aa'AS description
    UNIONSELECT'mmm order_id: 3 nn kk yw'AS description
    UNIONSELECT'mmm order_id: 1523 nn kk yw'AS description
  ) AS dataset

Solution 3:

or you can do this and save yourself the ugliness :

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

Solution 4:

There is no MySQL equivalent. The MySQL REGEXP can be used for matching strings, but not for transforming them.

You can either try to work with stored procedures and a lot of REPLACE/SUBSTRING logic, or do it in your programming language - which should be the easiest option.

But are you sure your data format is well chosen? If you need the order_id, wouldn't it make sense to store it in a different column, so you can put indexes, use joins and the likes?