mysql charindex(),分割逗号分隔的字符串 - >功能db.CHARINDEX不存在

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