分割逗号分隔的字符串 - >功能db.CHARINDEX不存在

问题描述

   
 
解决方案 是MySQL的等价物。
 
 
 
使用可能是一个更简洁的方法。像这样的东西（免责声明：未经测试）：
 $ 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 POS2So 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不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！