MySQL - Extracting numbers out of strings

Question

In a MySQL database, I have a table which contains itemID, itemName and some other fields.

Sample records (respectively itemID and itemName):

vaX652bp_X987_foobar, FooBarItem
X34_bar, BarItem
tooX56, TOOX_What

I want to write a query which gives me an output like:

652, FooBarItem
34, BarItem
56, TOOX_What

In other words, I want to extract out the number from the itemID column. But the condition is that the extracted number should be the number that occurs after the first occurence of the character "X" in the itemID column.

I am currently trying out locate() and substring() but could not (yet) achieve what I want..

EDIT: Unrelated to the question - Can any one see all the answers (currently two) to this question ? I see only the first answer by "soulmerge". Any ideas why ? And the million dollar question - Did I just find a bug ?!

Answer 1

That's a horrible thing to do in mysql, since it does not support extraction of regex matches. I would rather recommend pulling the data into your language of choice and processing it there. If you really must do this in mysql, using unreadable combinations of LOCATE and SUBSTRING with multiple CASEs is the only thing I can think of.

Answer 2

Why don't you try to make a third column where you can store, at the moment of the insertion of the record (separating the number in PHP or so), the number alone. So this way you use a little more of space to save a lot of processing.

Table:

vaX652bp_X987_foobar, 652, FooBarItem

X34_bar, 34, BarItem

tooX56, 56, TOOX_What