Split comma delimited string --> FUNCTION db.CHARINDEX does not exist

Question

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

Wiseguy · Accepted Answer · 2013-02-20 18:51:25Z

43

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

edited Feb 20, 2013 at 18:51

answered Mar 31, 2012 at 5:03

Wiseguy

20.9k9 gold badges68 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nikhil · Accepted Answer · 2012-10-21 12:00:42Z

4

MySQL does have a similar function: InStr or for the same syntax Locate.

edited Oct 21, 2012 at 12:00

Nikhil

16.2k20 gold badges66 silver badges81 bronze badges

answered Jun 17, 2012 at 16:22

bill

411 bronze badge

Collectives™ on Stack Overflow

Split comma delimited string --> FUNCTION db.CHARINDEX does not exist

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related