how to remove double space and enter in sql

METHOD 1 - WHILE LOOP AND REPLACE

By Using WHILE Loop, we can check the occurrences of double spaces, as Loop Condition.
Till the occurrence (Loop) fails, we need to alter every Double Spaces as Single Space.

DECLARE @TestString VARCHAR(200);  
SET @TestString='   Ex      ample   St  ring   '; 
 
WHILE CHARINDEX('  ',@TestString) <> 0
 SET @TestString = REPLACE(@TestString,'  ',' ');
 
SELECT @TestString AS RESULT;

If we also want to remove spaces at front and end of string, then use LTRIM AND RTRIM Functions.
If our SQL Server version is higher, then we can also use TRIM Function instead of LTRIM and RTRIM.

DECLARE @TestString VARCHAR(200);  
SET @TestString='   Ex      ample   St  ring   '; 
 
WHILE CHARINDEX('  ',@TestString) <> 0
 SET @TestString = REPLACE(@TestString,'  ',' ');
 
SELECT LTRIM(RTRIM(@TestString)) AS RESULT;

METHOD 2 - UNUSED CHARACTERS IN REPLACE

DECLARE @TestString VARCHAR(200);  
SET @TestString='   Ex      ample   St  ring   '; 
 
SELECT @TestString =
REPLACE(
    REPLACE(
        REPLACE(@TestString, ' ', ' ^')
    ,'^ ', '')
, '^', '');
 
SELECT LTRIM(RTRIM(@TestString)) AS RESULT;

We can also use any other character too instead of ‘^’.
Main thing is, it should not be one of the characters that is already present in input string.

METHOD 3 - ASCII NON-PRINTABLE CHARACTERS IN REPLACE

If we are not sure on what unused characters to use, then suggestion is to use ASCII Non-Printable characters instead of Unused characters like below.

DECLARE @TestString VARCHAR(200);  
SET @TestString='   Ex      ample   St  ring   '; 
 
SELECT @TestString = 
REPLACE(
    REPLACE(
            REPLACE(@TestString,' ',CHAR(17)+CHAR(18))
    ,CHAR(18)+CHAR(17),'')
,CHAR(17)+CHAR(18),' ');
 
SELECT LTRIM(RTRIM(@TestString)) AS RESULT;

CONCLUSION

We can use the above methods to Remove Duplicate as well as Multiple spaces to Single space. If we consider performance, Method 3 will be better than other two methods.