拆分逗号分隔的字符串 -->FUNCTION db.CHARINDEX 不存在

2021-12-28 00:00:00 function string split comma mysql

我需要将逗号分隔的字符串拆分为第二列我有下表:

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

我想将逗号后的数字拆分为第二列 POS2所以它应该喜欢

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 没有内置的 CHARINDEX() 函数.LOCATE() 将是 MySQL等价.

MySQL doesn't have a built-in CHARINDEX() function. LOCATE() would be the MySQL equivalent.

使用SUBSTRING_INDEX() 可能是一种更简洁的方法.像这样的东西(免责声明:未经测试):

Using SUBSTRING_INDEX() might be a more succinct way of doing this. Something like this (disclaimer: untested):

SUBSTRING_INDEX(POS, ',', 1) 用于 POS

SUBSTRING_INDEX(POS, ',', -1) 用于 POS2

顺便说一句，我可能误解了您要完成的任务，但您似乎想要UPDATE 现有行，而不是 INSERT 新行?类似的东西:

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);

相关文章