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

I have one table member_details with field "preferred_location" (varchar) that has co开发者_JAVA百科mma 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.

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.

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).

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();

}

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;