Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution 1


--

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('<A>'+ REPLACE(@Val,' ','</A><A>')+ '</A>' 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

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com