By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE [dbo].[Adress](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Address_Line_1] [nvarchar](500) NULL,
[Address_Line_2] [nvarchar](500) NULL,
CONSTRAINT [PK_Adress] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO [Adress](Address_Line_1, Address_Line_2) VALUES('123 street','Town');
INSERT INTO [Adress](Address_Line_1, Address_Line_2) VALUES('321 street 321 street 321 street','Town');
INSERT INTO [Adress](Address_Line_1, Address_Line_2) VALUES('456 streett','Town');
INSERT INTO [Adress](Address_Line_1, Address_Line_2) VALUES('789 street 789 street','Town');
4 rows affected
SELECT ads = STUFF((
SELECT ' ' + value
FROM STRING_SPLIT(Address_Line_1, ' ')
GROUP BY value
FOR XML PATH('')
), 1, 1, '') , Address_Line_1, Address_Line_2 FROM Adress
| ads | Address_Line_1 | Address_Line_2 |
|---|---|---|
| 123 street | 123 street | Town |
| 321 street | 321 street 321 street 321 street | Town |
| 456 streett | 456 streett | Town |
| 789 street | 789 street 789 street | Town |