string - How to expand comma-separated field into multiple rows in MySQL -

select id, ips users; 

query result

id    ips 1     1.2.3.4,5.6.7.8 2     10.20.30.40 3     111.222.111.222,11.22.33.44 4     1.2.53.43 

i'd run query produces following output

user_id     ip 1           1.2.3.4 1           5.6.7.8 2           10.20.30.40 3           111.222.111.222 3           11.22.33.44    4           1.2.53.43 

if don't mind using cursor, here's example:

 set nocount on; -- create sample table, @t declare @t table(id int, ips varchar(128)); insert @t values(1,'1.2.3.4,5.6.7.8') insert @t values(2,'10.20.30.40') insert @t values(3,'111.222.111.222,11.22.33.44') insert @t values(4,'1.2.53.43') insert @t values(5,'1.122.53.43,1.9.89.173,2.2.2.1')  select * @t  -- create table output, @u declare @u table(id int, ips varchar(128));  -- setup cursor declare xc cursor fast_forward select id, ips @t declare @id int, @ips varchar(128);  open xc fetch next xc @id, @ips while @@fetch_status = 0 begin         -- split apart ips, insert records table @u         declare @ix int;         set @ix = 1;         while (charindex(',',@ips)>0)         begin             insert @u select @id, ltrim(rtrim(substring(@ips,1,charindex(',',@ips)-1)))             set @ips = substring(@ips,charindex(',',@ips)+1,len(@ips))             set @ix = @ix + 1         end         insert @u select @id, @ips      fetch next xc @id, @ips end  select * @u