REMOVE EXTRA MULTIPLE SPACES IN STRING IN SQL SERVER 2005

Use following funtion to remove multiple spaces in string in SQL Server 2005

CREATE FUNCTION fnRemoveMiddleSpaces (@InputString VARCHAR(1024))
RETURNS VARCHAR(1024)
AS
BEGIN
WHILE CHARINDEX(SPACE(2),@InputString) > 0
SET @InputString =
REPLACE(@InputString,SPACE(2),SPACE(1))
RETURN @InputString
END
GO

DECLARE @Str varchar(256)
SET @Str = ‘Saurabh              Dolare            ‘
SELECT DATABEFORESPACES = @Str, DATABEFORESPACESREMOVED = dbo.fnRemoveMiddleSpaces (@Str)
GO

You can also use stuff function to remove spaces if you know the string in advance.

Eg.

SELECT STUFF ( ‘Gauri          Despande’ , 6 , 9 , ”)

Share Knowledge.We all are here to learn.

Pawan Kumar

Pawankkmr@hotmail.com