CODEWITHSUNDEEP
mysqlstringfunction
xjshiya
xjshiya

Reputation: 925

MySQL string functions

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!

Upvotes: 8

Views: 1548

Answers (3)

MvG
MvG

Reputation: 60858

As the currently accepted answer seems to cause problems later on, here is another alternative. It is heavily based on the solution by lc. but takes care of ordering.

First off: SQL tables have no well-defined row order. When you query for all rows of a table, those rows will be returned in a certain order, but that order is undefined, may or may not be related to the order in which rows were added, and may change unexpectedly. So when you want to process your rows in a given order, you should add a column to contain some kind of sequence number or smilar. That's the reason why lc. kept bugging you about the order of locations.

If you have such a column, called seq, then you can use the following query:

SELECT GROUP_CONCAT(loc SEPARATOR ' - ')
FROM (
 (SELECT 1 half, seq, SUBSTRING_INDEX(location, ' - ', 1) loc
  FROM location_list
  WHERE reservno='00004'
  ORDER BY seq
  LIMIT 1)
 UNION ALL
 (SELECT 2 half, seq, SUBSTRING_INDEX(location, ' - ', -1) loc
  FROM location_list
  WHERE reservno='00004')
 ORDER BY half, seq
) locs

The union will produce a list of the various locations, which is combined into a single string using the outermost select. The first part of the union yields the first half of the first part of the route, whereas the second part of the union will give you the second half of all parts. The order of the union result is undefined so far, so we need an overall ordering rule. We also need an ordering rule for the first half, so that we really choose the first part of the route with the limit clause.

Here is an sqlfiddle based on the one Omesh set up. For lack of a seq column, it uses the icode column to order things. Therefore the result differs from the one you expected, and produce Manila - Bohol - Cebu - Manila instead.

If you add a seq column, then you'll have to change the database schema, so you might as well change it in such a way that the two endpoints of each part are turned into two distinct columns. Combining columns using CONCAT is simple, but splitting them increases complexity of the queries both for the developer and the database engine.

If you cannot add a sequence column, because you don't have control over the database schema, then you're in trouble. Your comment here indicates that you're always looking for a cycle, but finding such a cycle from unordered data is best done using a map, which isn't readily available at the SQL level. You could achieve this through a stored procedure if you must, executing one select for each part of the jurney, but I'd rather tackle this at the application level if I were you.

Upvotes: 0

Omesh
Omesh

Reputation: 29081

You need to use SEPARATOR in GROUP_CONCAT function:

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

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

Example: SQLFIDDLE

It's not a good practice to store multiple values in same column, Better way could be:

reservno || icode || location_from || location_to
00004    || 00021 || Bohol         || Cebu
00004    || 00022 || Cebu          || Manila
00004    || 00014 || Manila        || Bohol

Upvotes: 6

lc.
lc.

Reputation: 116458

Here's one way. It separates out the two locations from your single location column, distincts it, then puts it back together SQL Fiddle:

SELECT GROUP_CONCAT(DISTINCT loc SEPARATOR ' - ')
FROM 
(
    SELECT SUBSTRING_INDEX(location, ' - ', 1) AS loc
    FROM location_list 
    WHERE reservno='00004'
    UNION ALL
    SELECT SUBSTRING_INDEX(location, ' - ', -1) AS loc
    FROM location_list 
    WHERE reservno='00004'
) separatedLocs

You might be looking for something more intelligent though? I sense "location" is more like a "from-to" kind of thing? I also question the decision of storing more than one value in your location column like this. You would be much better off storing a location1 and location2 as separate columns.

Upvotes: 3

Related Questions