Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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!

like image 840
xjshiya Avatar asked Aug 29 '12 05:08

xjshiya


People also ask

What is string function with example?

With string functions, you can create expressions in Access that manipulate text in a variety of ways. For example, you might want to display only part of a serial number on a form. Or, you might need to join (concatenate) several strings together, such as a last name and a first name.

Can you write functions in MySQL?

Just as you can create functions in other languages, you can create your own functions in MySQL.


1 Answers

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
like image 165
Omesh Avatar answered Oct 26 '22 16:10

Omesh