MySQL - 从字符串中提取数字

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

在MySQL数据库中,我有一个包含itemID,itemName和其他一些字段的表。

Sample records (respectively itemID and itemName):

样本记录(分别为itemID和itemName):

vaX652bp_X987_foobar, FooBarItem
X34_bar, BarItem
tooX56, TOOX_What

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

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.

换句话说,我想从itemID列中提取出数字。但条件是提取的数字应该是itemID列中第一个出现字符“X”后出现的数字。

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

我目前正在尝试locate()和substring()但是还没有(还)实现我想要的东西..

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

编辑:与问题无关 - 任何人都能看到这个问题的所有答案(目前是两个)吗?我只看到“soulmerge”的第一个答案。有什么想法吗?百万美元的问题 - 我刚刚发现了一个错误吗?!

3 个解决方案

#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.

这在mysql中是一个可怕的事情,因为它不支持提取正则表达式匹配。我宁愿建议将数据拉入您选择的语言并在那里进行处理。如果你真的必须在mysql中这样做,那么使用不可读的LOCATE和SUBSTRING与多个CASE的组合是我唯一能想到的。

#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.

为什么不尝试在插入记录时将第三列放在可以存储的位置(用PHP分隔数字),单独使用数字。所以这样你就可以使用更多的空间来节省大量的处理。

Table:

vaX652bp_X987_foobar, 652, FooBarItem

vaX652bp_X987_foobar,652,FooBarItem

X34_bar, 34, BarItem

X34_bar,34,BarItem

tooX56, 56, TOOX_What

太X56,56,TOOX_What

#3

This isn't so unreadable :

这不是那么难以理解:

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

#1

#2