Convert column to multiple rows without Split_Lines - SQL Server 2008

Question

I would like to convert the following SQL table:

User_Index   Emails
------------------------
  5          test@db.com;test1@db.com
  10         re2@db.com;re3@db.com

into:

  User_Index   Emails
------------------------
  5          test@db.com
  5          test1@db.com
  10         re2@db.com
  10         re3@db.com

I am using SQL Server 2008 so SPLIT_LINES function does not work, I am trying to split the emails column by the semi-colon value.

Answer 1

One method is a recursive CTE:

with cte as (
      select user_index, convert(varchar(max), null) as email, convert(varchar(max), emails + ';') as rest
      from t
      union all
      select user_index, left(rest, charindex(';', rest) - 1), stuff(rest, 1, charindex(';', rest) + 1, '')
      from cte
      where rest <> ''
     )
select user_index, email
from cte
where email is not null;

Here is a db<>fiddle.