How to remove extra space between the string in SQL Server Database

We get such type of condition when we concatenate multiple columns data into one output column and those column data have extra space.

For E.g: We have declared one variable and entered some dummy data as shown below.

Query:-

DECLARE @TestString1 AS VARCHAR(100)

SET @TestString1 = 'Address 1,      Address2,    Address3,     123'

print @TestString1;

Output:-   Address 1,      Address2,    Address3,     123

Here we are getting extra space between the addresses and condition to remove the extra space between the string then

Query:-

DECLARE @TestString2 AS VARCHAR(100)

SET @TestString2 = 'Address 1,      Address2,    Address3,     123'

SET @TestString2= REPLACE(REPLACE(REPLACE(@TestString2,' ',' %'),'% ',''),'%','')

print @TestString2;

Output:- Address 1, Address2, Address3, 123

In this query, we have replaced the extra space through a single space with the help of a wildcard operator.

What other ideas can you add to this post that I may have not mentioned?