MySQL 删除所有字段的前两个字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10389083/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Remove first two characters of all fields
提问by Norse
I have some data that looks like this:
我有一些看起来像这样的数据：
C:10
R:200
N/A
E:3
N/A
N:77
I'm trying to remove the first two characters for each row, and skip the rows with N/AI've been trying to figure out how to do this with SUBSTRINGbut have had no luck.
我试图删除每一行的前两个字符，并跳过这些行，N/A因为我一直试图弄清楚如何做到这一点，SUBSTRING但没有运气。
UPDATE d1
SET d1_val = SUBSTRING(d1_val, 1, LENGTH(d1_val)2)
回答by Kevin DiTraglia
Try
尝试
UPDATE d1
SET d1_val = SUBSTRING(d1_val, 3)
WHERE d1_val <> 'N/A'
回答by Serge S.
More safe query regarding multiple executes is using LIKE '_:%':
关于多次执行的更安全的查询是使用LIKE '_:%'：
UPDATE d1
SET d1_val = SUBSTRING(d1_val, 3)
WHERE d1_val LIKE '_:%'
It will change every line which looks like C:10, E:100etc. but not 10, 100after one previous query run.
它会改变每一行，它看起来像C:10，E:100等等。但不是10，100一个以前的查询运行后。
(Assuming there is always one letter before :. If more - use LIKE '%:%')
（假设之前总是有一个字母:。如果更多 - 使用LIKE '%:%'）
回答by Dirk Zaal
UPDATE d1
SET d1_val = SUBSTRING_INDEX(d1_val , ":",-1);
Does the job also, leaves "N/A" as it is.
这份工作也做，保持“N/A”不变。
