RegEx in MySQL?

Question

When I do

SELECT resrev_meta FROM resourcerevs WHERE resrev_meta LIKE '%attr%';

I get results like this

<attr><fileid>131</fileid></attr> 
<attr><fileid>326</fileid><width>360</width><height>640</height></attr>

Question

Is it possible to have a RegEx that would only output the number between <fileid> and </fileid>?

do you want only the fieldid even if there are multiple attributes ? — FSP
– FSP, Commented Jun 26, 2012 at 17:04
I had a similar problem and was elated to find out that mysql supported XPATH expressions. — ZnArK
– ZnArK, Commented Jun 26, 2012 at 17:12

ZnArK · Accepted Answer · 2013-04-02 03:36:48Z

5

Regex is probably not what your looking for. mysql supports xpath expressions.

This should give you what you need:

SELECT ExtractValue(resrev_meta,'//fileid')  AS fileid 
     FROM resourcerevs 
     WHERE resrev_meta LIKE '%attr%';

http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html#function_extractvalue

mysql> SELECT
    ->   ExtractValue('<a>ccc<b>ddd</b></a>', '/a') AS val1,
    ->   ExtractValue('<a>ccc<b>ddd</b></a>', '/a/b') AS val2,
    ->   ExtractValue('<a>ccc<b>ddd</b></a>', '//b') AS val3,
    ->   ExtractValue('<a>ccc<b>ddd</b></a>', '/b') AS val4,
    ->   ExtractValue('<a>ccc<b>ddd</b><b>eee</b></a>', '//b') AS val5;

+------+------+------+------+---------+
| val1 | val2 | val3 | val4 | val5    |
+------+------+------+------+---------+
| ccc  | ddd  | ddd  |      | ddd eee |
+------+------+------+------+---------+

edited Apr 2, 2013 at 3:36

answered Jun 26, 2012 at 17:08

ZnArK

1,5391 gold badge12 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bohemian · Accepted Answer · 2012-06-26 17:08:39Z

3

No, not using regex. Mysql doesn't support regex-based replacement or group matching etc.

The only regex support is RLIKE or REGEXP (they are synonyms), which is used to match a column value.

answered Jun 26, 2012 at 17:08

Bohemian♦

427k103 gold badges604 silver badges750 bronze badges

Comments

user21926 · Accepted Answer · 2012-06-26 17:09:23Z

3

No, but you can do something hacky like this if you want (pardon the long lines):

SELECT
  SUBSTRING(
    resrev_meta,
    INSTR(resrev_meta, '<fileid>') + LENGTH('<fileid>'),
    INSTR(resrev_meta, '</fileid>') - INSTR(resrev_meta, '<fileid>') - LENGTH('<fileid>')) AS fileid
FROM resourcerevs WHERE resrev_meta LIKE '%attr%';

answered Jun 26, 2012 at 17:09

user21926

Collectives™ on Stack Overflow

RegEx in MySQL?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related