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/A I've been trying to figure out how to do this with SUBSTRING but have had no luck.
我试图删除每一行的前两个字符，并跳过N / A的行我一直试图弄清楚如何使用SUBSTRING做这个但没有运气。
UPDATE d1
SET d1_val = SUBSTRING(d1_val, 1, LENGTH(d1_val)2)
2 个解决方案
#1
19
Try
尝试
UPDATE d1
SET d1_val = SUBSTRING(d1_val, 3)
WHERE d1_val <> 'N/A'
#2
4
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:100 etc. but not 10, 100 after one previous query run.
在上一次查询运行之后，它将改变看起来像C：10，E：100等但不是10,100的每一行。
(Assuming there is always one letter before :. If more - use LIKE '%:%')
（假设之前总是有一个字母：。如果更多 - 使用LIKE'％：％'）
#1
19
Try
尝试
UPDATE d1
SET d1_val = SUBSTRING(d1_val, 3)
WHERE d1_val <> 'N/A'
#2
4
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:100 etc. but not 10, 100 after one previous query run.
在上一次查询运行之后，它将改变看起来像C：10，E：100等但不是10,100的每一行。
(Assuming there is always one letter before :. If more - use LIKE '%:%')
（假设之前总是有一个字母：。如果更多 - 使用LIKE'％：％'）