分割逗号分隔的字符串 - >功能db.CHARINDEX不存在 [英] Split comma delimited string --> FUNCTION db.CHARINDEX does not exist
问题描述
CL1 POS POS2 LENGHT ALLELE
1 3015108,3015109 5 A
2 3015110,3015200 10 B
3 3015200,3015300 15 C $ b $ 4 4 3015450,3015500 20 D
5 3015600, 3015700 15 E
我想将逗号后面的数字分成第二列POS2
所以它应该是这样的
CL1 POS POS2 LENGHT ALLELE
1 3015108 3015109 5 A
2 3015110 3015200 10 B
3 3015200 3015300 15 C
4 3015450 3015500 20 D
5 3015600 3015700 15 E
所以我问过这个问题以下是:
pre $ INSERT INTO MyTable(POS2)
$ p $ MySQL没有内置的
SELECT RIGHT(POS,CHARINDEX(',',POS) + 1)FROM MyTable;
它返回一个错误:
错误1305(42000):FUNCTION test.CHARINDEX不存在
CHARINDEX()
函数。
LOCATE()
是MySQL的等价物。 使用 SUBSTRING_INDEX()
可能是一个更简洁的方法。像这样的东西(免责声明:未经测试):
$ b
SUBSTRING_INDEX(POS,',',1)
/ p>
SUBSTRING_INDEX(POS,',',-1)
POS2
另外,我可能会误解你想要完成的任务,但看起来你可能想要 UPDATE
现有的行,而不是 INSERT
新的行?例如:
UPDATE MyTable SET POS2 = SUBSTRING_INDEX(POS,',',-1);
UPDATE MyTable SET POS = SUBSTRING_INDEX(POS,',',1);
I need to split comma delimited string into a second columns I have the following table :
CL1 POS POS2 LENGHT ALLELE
1 3015108,3015109 5 A
2 3015110,3015200 10 B
3 3015200,3015300 15 C
4 3015450,3015500 20 D
5 3015600,3015700 15 E
I want to split the numbers after the comma into a second column POS2 So it should like that
CL1 POS POS2 LENGHT ALLELE
1 3015108 3015109 5 A
2 3015110 3015200 10 B
3 3015200 3015300 15 C
4 3015450 3015500 20 D
5 3015600 3015700 15 E
So I've queried the following :
INSERT INTO MyTable (POS2)
SELECT RIGHT(POS, CHARINDEX(',', POS) + 1 ) FROM MyTable ;
It returns an error :
ERROR 1305 (42000): FUNCTION test.CHARINDEX does not exist
MySQL doesn't have a built-in CHARINDEX()
function. LOCATE()
would be the MySQL equivalent.
Using SUBSTRING_INDEX()
might be a more succinct way of doing this. Something like this (disclaimer: untested):
SUBSTRING_INDEX(POS, ',', 1)
for POS
SUBSTRING_INDEX(POS, ',', -1)
for POS2
As an aside, I may be misunderstanding what you're trying to accomplish, but it looks like you might want to UPDATE
existing rows, not INSERT
new ones? Something like:
UPDATE MyTable SET POS2 = SUBSTRING_INDEX(POS, ',', -1);
UPDATE MyTable SET POS = SUBSTRING_INDEX(POS, ',', 1);
这篇关于分割逗号分隔的字符串 - >功能db.CHARINDEX不存在的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!