MySQL：如何从列中删除字符串
很简单 我有一列这样的字符串：
abc1
abc2
abc3
abc4
abc5
我需要从该列中删除“abc”并只保留右侧的数字，因此该列将只有这样的数字：
1
2
3
4
5
我想过这样的问题，但它不起作用
update table2
-> set col1 = case when 'abc1' then 1
-> else end;
我知道如何连接文本，我不知道如何撤消它...请帮忙^^;
Very simple
I have a column of string like that :
abc1
abc2
abc3
abc4
abc5
I need to remove 'abc' from this column and keep only the number on the right, so column would have only the number like :
1
2
3
4
5
I thought about smth like that but it doesn't work
update table2
-> set col1 = case when 'abc1' then 1
-> else end;
I know how to concat text, I don't know how to undo it... Help please ^^;
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
@McArthey 已经暗示过这一点，但是当“abc”始终为“abc”（即长度不变）时，这很容易做到。）
在各种 字符串函数 是一个特别的函数：
RIGHT()。这允许您从字符串中选择固定数量的字符。例如与
LENGTH()函数，您可以得出结论，这些数字是第三个字符之后的任何数字。即显然我使用的是硬字符串（
'abc3'），但这些可以轻松地用列名称替换。这里需要注意的是，这些都是基于固定长度的字母前缀。示例中的“abc”变量越多（更改），从列中选取数值就越困难。
@McArthey already hinted at it, but this is easy to do when the "abc" is consistently "abc" (i.e. the length doesn't change.)
Amongst the various string functions is one in particular:
RIGHT(). This allows you to select a fixed number of characters from a string. e.g.Coupled with the
LENGTH()function, you can conclude the numbers are anything past the 3rd character. i.e.Obviously I'm using hard strings (
'abc3'), but these can easily be replaced with column names.The caveat here is that these all are based on fixed length letter prefixes. The more variable (changing) the "abc" in your example is, the harder picking the numeric value out of the column becomes.
如果这些是单位数字值，您可以使用
您还可以找到 REGEXP 文档如果更复杂的话很有用。
如果您尝试修改列，则必须单独获取这些值，然后将它们重新连接在一起。很难给出准确的答案，因为我不知道你想要完成什么，但你可以用 SUBSTR 做一些事情来获取单独的值。
获取 'abc'：SUBSTR(column, 1,3)
获取数字：SUBSTR(column, 4)
If these are single digit values you could use
You may also find the REGEXP docs useful if it is more complex.
If you are trying to modify the column you will have to take the values separately and then concatenate them back together. It's difficult to give a precise answer since I don't know what you're trying to accomplish but you could do something with SUBSTR to grab the separate values.
Get 'abc': SUBSTR(column, 1,3)
Get digits: SUBSTR(column, 4)