to separate comma separated values of field and add them in integer field of temp table

Question

I have one table member_details with field "preferred_location" (varchar) that has comma separated values like "19,20,22" that come from a listbox selection ....

Now I also have another table city_master having field "city_id" (int) and "city_name" (varchar)...

Now I want to separate "preferred_location" (varchar) values and to add them in integer field of temp table so I can make an inner join between city_id(int) of the temp table and city_id(int) of city_master and then can get city name from city_name of city_master...

This is all stuff I need in MySQL - either a stored procedure or a function. I am using it with c#.net.

This is why you don't use un-normalized data fields - they're a pain to process. Change the structure of your preferred location - it belongs in a separate table from the rest of member_details. — Jonathan Leffler, Dec 30 '09 at 07:54

score 1 · Answer 1 · answered Dec 30 '09 at 07:18

Frankly, this sounds like a bad design. If you need the integers values separately, then modify your database structure accordingly, and save the values separately to begin with.

I mean, you see where it leads to - because you stored the values as a list in a string, you have maneuvered yourself into a position where you need to unwind the values each time you want to join the tables.

That's like putting the horse behind the wagon.

score 0 · Answer 2 · answered Dec 30 '09 at 07:20

0

If these integers are small, like 19,20,22 etc just use smaller 16 or 8 bit integers (as supported by your database) and it should not take much more space than a string (possibly even less).

answered Dec 30 '09 at 07:20

Tarydon

5,097
23
24

score 0 · Answer 3 · answered Dec 30 '09 at 08:56

Made up some mock up example, but this should work with LinqToMySql as well.

class user {
public string name {get;set;}
public int id {get;set;}
}
class member_detail {
public int user_id {get;set;}
public string prefered {get;set;}
}

class city_master{
public int code {get;set;}
public string name {get;set;}
}
void Main()
{
var users = new List<user>();
users.Add(new user(){name = "Mary",id = 1});
users.Add(new user(){name = "John",id=2});
    var details = new List<member_detail>() ;
    details.Add(new member_detail(){user_id=1,prefered="1,2,3"});
    details.Add(new member_detail(){user_id=2,prefered="3,5"});
    var cities = new List<city_master>();

    cities.Add(new city_master(){code =1,name="Moscow"});
    cities.Add(new city_master(){code =2,name="London"});
    cities.Add(new city_master(){code =3,name="Paris"});
    cities.Add(new city_master(){code =4,name="Rome"});
    cities.Add(new city_master(){code =5,name="Madrid"});

    users.Select(u=>new {u.name,cities=
    details.Where(d=>d.user_id==u.id)
    .SelectMany(d=>d.prefered.Split(','))
    .Join(cities,c=>c,d=>d.code.ToString(),(a,b)=>new {b.name})}).Dump();

}

score 0 · Answer 4 · edited Dec 15 '11 at 15:00

thanks for your suggestion but in my case it is better to store ids of preferred location cities as comma separated.

I have a procedure that makes a temporary table and then I can use inner join with city_master table to get city names.

Create Procedure parseAndStoreList(in thingId int, in i_list varchar (128), 
                                   out returnCode smallInt) 
BEGIN 
         DECLARE v_loopIndex default 0;
         DECLARE Exit Handler for SQLEXCEPTION 
BEGIN
        call saveAndLog(thingId, 'got exception parsing list');
        set returnCode = -1;
END;

call dolog(concat_ws('got list:', i_list)); 
          pase_loop: LOOP set v_loopIndex = v_loopIndex + 1; 
call dolog(concat_wc(',', 'at loop iteration ', v_loopIndex);
          LOOOP parse_loop; 
set returnCode = 0;
END;

to separate comma separated values of field and add them in integer field of temp table

4 Answers4