CODEWITHSUNDEEP
mysqlstring
Vijay Dev
Vijay Dev

Reputation: 27476

MySQL - Extracting numbers out of strings

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 ?!

Upvotes: 2

Views: 3330

Answers (3)

David N. Jafferian
David N. Jafferian

Reputation: 466

This isn't so unreadable :

SELECT 0+SUBSTRING(itemID, LOCATE("X", itemID)+1), itemName FROM tableName

Upvotes: 1

Diego
Diego

Reputation:

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

Upvotes: 1

soulmerge
soulmerge

Reputation: 75704

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.

Upvotes: 1

Related Questions