First of all, don't do this in mysql . If you got a dump of the database, using cut utility u can easily extract the second field in the delimited by the comma.. and then split them into different columns in the table when you want to insert them into the mysql database. If you are still looking for something to do in mysql, you can do it with a little work around.. You will have to create a different table with my solution, and you can create it as temporary table..
CREATE TABLE your_new_table SELECT SUBSTRING_INDEX(a1,',',3) AS cut_value FROM your_original_table; This will create a new table and if you do a select * on it you will see: +-----------+ | cut_value | +-----------+ | ,1,4 | | ,1,3 | | ,1,5 | | ,1,9 | | ,1,40 | +-----------+ 5 rows in set (0.23 sec) and in this table, you can do: SELECT SUBSTRING_INDEX(cut_value,',',-1) AS value_i_want FROM your_new_table; And it will give you: +--------------+ | value_i_want | +--------------+ | 4 | | 3 | | 5 | | 9 | | 40 | +--------------+ 5 rows in set (0.00 sec) Hope this helps.. Gurhan On Sat, 2002-11-16 at 08:45, Paul van Brouwershaven wrote: > Hi, > > I have a colum with this values : > > ,1,4,5,66,247,7, > ,1,3,5,62,767,6, > ,1,5,5,11 > ,1,9,5,36,7677,9, > ,1,40,55,66,444,3, > > I want to get whis values : (second field) > > 4 > 3 > 5 > 9 > 40 > > I have tried this : > > REPLACE(SUBSTRING_INDEX(value, ',', 3), ',','') > > But the following is returned : > > 14 > 13 > 15 > 19 > 140 > > I have alse tried the following query : > > select > mid(path,locate(2,path,",")+1,(locate(locate(2,path,",")+1,path,",")-loc > ate(2,path,",")-1)) from path_table > > Regards, > > Paul > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
