SQL Server Portal

How to remove extra spaces from string value is a common issue and we usually come across this issue while massaging the data. I used this solution as a part of a solution in one of my earlier solutions.

Let me create a sample to demonstrate the solution.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
--DROP TABLE tbl_sample
--GO
--Create table
CREATE TABLE tbl_sample
(
[ID] INT,
[NAME] VARCHAR(100)
)
GO
--Insert few records in the table
--Note : Names have been taken from adventureworks2012 database.
INSERT INTO tbl_sample VALUES (1,'Terri    Lee          Duffy')
INSERT INTO tbl_sample VALUES (2,'Roberto     Tamburello')
INSERT INTO tbl_sample VALUES (3,'Rob   Walters')
INSERT INTO tbl_sample VALUES (4,'Gail   A        Erickson')
INSERT INTO tbl_sample VALUES (5,'Gigi      N      Matthew')
GO
--Browse table
SELECT
[ID]
,[NAME] AS [String with extra spaces]
FROM tbl_sample
--OUTPUT

SOLUTION 1 : Using REPLACE(string) Function
In this solution, we need to use the built-in function REPLACE to remove extra spaces from string value.
Given below is the script.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
SELECT
[ID]
,[NAME] AS [String with extra spaces]
,REPLACE(REPLACE(REPLACE([NAME]
,CHAR(32),'()'),')(',''),'()',CHAR(32))
AS [String without extra spaces]
FROM tbl_sample
GO
--OUTPUT

SOLUTION 2 : Using User Defined Function
In this solution, we need to create a User Defined Function to remove extra spaces from string using XML.
Given below is the script.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
--DROP FUNCTION dbo.[UDF_Remove_Extra_Space_From_String]
--GO
CREATE FUNCTION dbo.[UDF_Remove_Extra_Space_From_String]
(
@String VARCHAR(MAX) -- Variable for string
)
RETURNS VARCHAR(MAX)
BEGIN
DECLARE @Xml XML
DECLARE @Removed_Extra_Space VARCHAR(MAX)
DECLARE @delimiter VARCHAR(5)

SET @delimiter=' '
SET @Xml = CAST(('<a>'+REPLACE(@String,@delimiter,'</a><a>')+'</a>')
AS XML)

;WITH CTE AS (SELECT
A.value('.', 'VARCHAR(MAX)') AS [Column]
FROM @Xml.nodes('A') AS FN(A))

SELECT @Removed_Extra_Space=REPLACE(
Stuff((
SELECT ';' + A.[Column]
FROM CTE A
WHERE ISNULL(A.[Column],'') <>''
FOR XML PATH('')),1,1,''),';',' ')

RETURN (@Removed_Extra_Space)
END
GO

SELECT
[ID]
,[NAME] AS [String with extra spaces]
,dbo.[UDF_Remove_Extra_Space_From_String] ([Name])
AS [String without extra spaces]
FROM tbl_sample
GO
--OUTPUT