SQL Puzzle | How to Remove Huge Multiple Spaces from a DB Fields

In this puzzle you have multiple spaces between the characters in the string. Here you have to replace all the multiple and consecutive spaces with a single space.

Please check out the sample input values and sample expected output below.

Sample Input

Id Vals
1 ABC DEF K
2 FGR
3 FG T
4 A
5 Pawan
6 A
7 A

Expected Output

Id OldVals ValueAfterRemovingSpaces
1 ABC DEF K ABC DEF K
2 FGR FGR
3 FG T FG T
4 A A
5 Pawan Pawan
6 A A
7 A A

Script

Use below script to create table and insert sample data into it.

 ```-- -- CREATE TABLE testSpaces ( Id INT ,Vals VARCHAR(MAX) ) GO INSERT INTO testSpaces VALUES (1,'ABC DEF K'), (2,' FGR'), (3,'FG T'), (4,' A '), (5,'Pawan'), (6,' A '), (7,'A ') GO SELECT * FROM testSpaces -- -- ```

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

 ``` -- IF OBJECT_ID('tempdb..#ills') IS NOT NULL DROP TABLE #ills IF OBJECT_ID('tempdb..#Final') IS NOT NULL DROP TABLE #Final CREATE TABLE #Final ( Id INT , OldVals VARCHAR(MAX) , ValueAfterRemovingSpaces VARCHAR(MAX) ) SELECT * INTO #ills FROM testSpaces DECLARE @Id AS INT DECLARE @Val AS VARCHAR(MAX) DECLARE @Curr AS VARCHAR(1) DECLARE @Start AS INT DECLARE @End AS INT WHILE EXISTS(SELECT TOP 1 1 FROM #ills) BEGIN SELECT TOP 1 @Val = Vals , @Id = Id FROM #ills SET @Val = LTRIM(RTRIM(@Val)) IF CHARINDEX(' ', @Val , 0) > 0 BEGIN DECLARE @finalString AS VARCHAR(MAX) = '' SELECT @finalString = STUFF((SELECT ' ' + SplittedString FROM ( SELECT SplittedString FROM ( SELECT CAST(''+ REPLACE(@Val,' ','')+ '' AS XML) Xmlcol ) s CROSS apply ( SELECT ProjectData.D.value('.', 'VARCHAR(MAX)') as SplittedString FROM s.xmlcol.nodes('A') as ProjectData(D) )a WHERE SplittedString <> '' )k FOR XML PATH('')),1,1,'') INSERT INTO #Final SELECT @Id , @Val , @finalString END ELSE BEGIN INSERT INTO #Final SELECT @Id, @Val ,@Val END DELETE FROM #ills WHERE Id = @Id END SELECT * FROM #Final -- ```