MySQL string functins

[xjshiya]

I have a a table with the following data:

reservno || icode || location
00004    || 00021 || Bohol - Cebu
00004    || 00022 || Cebu - Manila
00004    || 00014 || Manila - Bohol

I use this query to retrieve the concatenated value of location.

SELECT GROUP_CONCAT(location) from location_list where reservno='00004';

The query result looks like this:

GROUP_CONCAT(location)
Bohol - Cebu,Cebu - Manila,Manila - Bohol

But what I want to do is for the query to look like this: Bohol - Cebu - Manila - Bohol. I would like to merge the result like that. How can I achieve this? I'm not that familiar with MySQL string functions so I need some ideas on how to make this work. Any help will be appreciated. Thanks a lot!

[smantscheff]

I don't know of any MySQL string function which would cut any strings from the "," up to the next "-". MySQL does not offer regular expression functions which might do the trick. You can write a user defined function, though, which cuts those parts and apply it to the query result, like in

SELECT MyCutFunction(GROUP_CONCAT(location)) from location_list where reservno='00004

Or you can do it like this (adapt the group_concat separator to your needs):

drop table if exists destinations;

CREATE TABLE destinations
    (reservno int, icode int, location varchar(14));

INSERT INTO destinations
    (reservno, icode, location)
VALUES
    (00004, 00021, 'Bohol - Cebu'),
    (00004, 00022, 'Cebu - Manila'),
    (00004, 00014, 'Manila - Bohol');

select 
concat(
(select substr(location,1,locate('-',location) - 1) from destinations limit 1),
group_concat(substr(location,locate('-',location)))
)
 from destinations;

[xjshiya]

SELECT  GROUP_CONCAT(IF((@var_ctr := @var_ctr + 1) = @cnt, 
                        location, 
                        SUBSTRING_INDEX(location,' - ', 1)
                       ) SEPARATOR ' - ') AS locations

FROM location_list, 
     (SELECT @cnt := COUNT(1), @var_ctr := 0 
      FROM location_list 
      WHERE reservno='00004'
     ) dummy
WHERE reservno='00004';

This did the trick. Thank you for your reply @smantscheff.