mysql - How to split one column into multiple rows in SQL? -

this question has answer here:

• sql split values multiple rows 5 answers

i have table this.

id  items 1  1|2|3 2  3|4 ... 

now, want split items multiple rows, this.

id  item 1  1 1  2 1  3 2  3 2  4 ... 

can sql job?

you shouldn't store data in format in database due index usages etc. in cases, can't avoid it, if foreign application delivers information in way example. if have store way , disaggregate store in format can use following user defined function achieve it.

create function dbo.udf_split (@string nvarchar(max), @delimiter nchar(1))  returns @results table (items nvarchar(max)) begin     declare @index int     declare @slice nvarchar(max)      set @index = 1      if @string null return      while @index != 0     begin         select @index = charindex(@delimiter, @string)          if @index != 0             select @slice = left(@string, @index - 1)         else             select @slice = @string              insert @results(items) values (ltrim(rtrim(@slice)))                 select @string = right(@string, len(@string) - @index)              if len(@string) = 0 break         end     return end go