SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table MyData
(
  Id int,
  MyAddress nvarchar(200)
);

insert into MyData (Id, MyAddress) values
(1, 'address line 1' + char(10) + 'address line 2' + char(10) + 'address line 3'),
(2, 'short address' + char(10) + 'with only 2 lines'),
(3, '1234 Main Street' + char(10) + 'Unit 123' + char(10) + 'BOX 123');

select * from MyData;


Id
MyAddress


1
address line 1
address line 2
address line 3

2
short address
with only 2 lines

3
1234 Main Street
Unit 123
BOX 123


with cte as (
  select md.Id, md.MyAddress, 1 as Starts, charindex(char(10), md.MyAddress) as Pos
  from MyData md
    union all
  select cte.Id, cte.MyAddress, cte.Pos + 1, charindex(char(10), cte.MyAddress, cte.Pos + 1)
  from cte
  where cte.Pos > 0
)
select cte.*
from cte
order by cte.Id;


Id
MyAddress
Starts
Pos


1
address line 1
address line 2
address line 3
1
15

1
address line 1
address line 2
address line 3
16
30

1
address line 1
address line 2
address line 3
31
0

2
short address
with only 2 lines
15
0

2
short address
with only 2 lines
1
14

3
1234 Main Street
Unit 123
BOX 123
1
17

3
1234 Main Street
Unit 123
BOX 123
18
26

3
1234 Main Street
Unit 123
BOX 123
27
0


with cte as (
  select md.MyAddress, 1 as Starts, charindex(char(10), md.MyAddress) as Pos, 1 as RowNum
  from MyData md
    union all
  select cte.MyAddress, cte.Pos + 1, charindex(char(10), cte.MyAddress, cte.Pos + 1), RowNUm+1
  from cte
  where cte.Pos > 0
),
split as
(
  select cte.MyAddress,
         cte.RowNum,
         substring(cte.MyAddress, cte.Starts, case when cte.Pos > 0 then cte.Pos-cte.Starts else len(cte.MyAddress) end) as Line
  from cte
)
select p.MyAddress,
       p.[1] as Line1,
       p.[2] as Line2,
       p.[3] as Line3
from split s
pivot (max(s.Line) for s.RowNum in ([1], [2], [3])) p;


MyAddress
Line1
Line2
Line3


1234 Main Street
Unit 123
BOX 123
1234 Main Street
Unit 123
BOX 123

address line 1
address line 2
address line 3
address line 1
address line 2
address line 3

short address
with only 2 lines
short address
with only 2 lines
null

Id	MyAddress
1	address line 1 address line 2 address line 3
2	short address with only 2 lines
3	1234 Main Street Unit 123 BOX 123

MyAddress	Line1	Line2	Line3
1234 Main Street Unit 123 BOX 123	1234 Main Street	Unit 123	BOX 123
address line 1 address line 2 address line 3	address line 1	address line 2	address line 3
short address with only 2 lines	short address	with only 2 lines	null