MySQL 拆分逗号分隔的字符串 --> FUNCTION db.CHARINDEX 不存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9953114/
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
Split comma delimited string --> FUNCTION db.CHARINDEX does not exist
提问by madkitty
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
我想将逗号后的数字拆分为第二列 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
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
回答by Wiseguy
MySQL doesn't have a built-in CHARINDEX()function. LOCATE()would be the MySQL equivalent.
MySQL 没有内置CHARINDEX()函数。LOCATE()将是 MySQL 等价物。
Using SUBSTRING_INDEX()might be a more succinct way of doing this. Something like this (disclaimer: untested):
使用SUBSTRING_INDEX()可能是一种更简洁的方法。像这样的东西（免责声明：未经测试）：
SUBSTRING_INDEX(POS, ',', 1)for POS
SUBSTRING_INDEX(POS, ',', 1)POS机
SUBSTRING_INDEX(POS, ',', -1)for POS2
SUBSTRING_INDEX(POS, ',', -1)POS2
As an aside, I may be misunderstanding what you're trying to accomplish, but it looks like you might want to UPDATEexisting rows, not INSERTnew ones? Something like:
顺便说一句，我可能误解了您要完成的任务，但看起来您可能想要UPDATE现有行，而不是INSERT新行？就像是：
UPDATE MyTable SET POS2 = SUBSTRING_INDEX(POS, ',', -1);
UPDATE MyTable SET POS = SUBSTRING_INDEX(POS, ',', 1);
回答by bill
MySQLdoes have a similar function: InStror for the same syntax Locate.
MySQL确实有类似的功能：InStr或为相同的语法定位。
