Selecting part of a field with a regex

Question

I've a table where a 3rd party component stores urls, i would like to get only the id parameter from this url.

With PHP i can do it like this:

$subject = "index.php?option=com_content&catid=2&id=456&view=article"; //mysql query result
$pattern = '*[&?]id=([0-9]+)*'; //matches either ?id=456 or &id=456
preg_match($pattern, $subject, $matches);
echo $matches[1];//prints 456

The number matched would be part of a new query:

SELECT name FROM table1 WHERE id=the_match

Now, i think it would be a lot faster to do it directly via mysql, something like

SELECT name FROM table1 WHERE id = (SELECT REGEX_MATCH('*[&?]id=([0-9]+)*', '$1') FROM table2 WHERE uniquefield1 = 'fred')

Obviously SELECT REGEX_MATCH('*[&?]id=([0-9]+)*', '$1') FROM table2 WHERE uniquefield1 = 'fred') is completely invented, just to say that i want to select the first group matched from regex and use it to make the WHERE clause work.

Is it possible to do something like this with MySQL?

Answer 1

This answer is obsolete. MySql has better regex support.

No, sad to say MySQL doesn't have a way to apply a regex to a column's contents in a SELECT clause, only a WHERE clause.

But you can use ordinary (non-regex) string manipulation functions to do this. If the column containing your ampersand-separated parameter string is named url, you can get the id number with this fine string expression, which finds your id number.

  CAST(RIGHT(url, LENGTH(url) - 3 - LOCATE('&id=', url)) AS SIGNED INTEGER)

So, if you want a list of id values from the url columns of table1, you could use this SELECT query.

SELECT CAST(RIGHT(url, LENGTH(url) - 3 - 
                       LOCATE('&id=', url)) AS SIGNED INTEGER) AS id
  FROM table1
 WHERE url REGEXP '&id=[0-9]+'

As you can see this uses the regexp search function to locate the appropriate rows.

There is nothing fast about this. Regexp matching can't exploit a MySQL index. If you have the choice of loading your table with the id column pre-extracted you'll be much better off searching when your table gets big.

Answer 2

Is possible to use this package: mysql-udf-regexp

The functions implemented by this package are:

REGEXP_LIKE(text, pattern [, mode])
REGEXP_SUBSTR(text, pattern [,position [,occurence [,mode]]])
REGEXP_INSTR?(text, pattern [,position [,occurence [,return_end [,mode]]]])
REGEXP_REPLACE?(text, pattern, replace [,position [,occurence [,return_end [,mode]]])

Very similar to the Oracle SQL functions.