How to replace multiple space characters in a row with a single space. For example, the string “22 Main Steet Roma ” should look like “’22 Main Steet Roma “.
MS SQL Server
Create Function
CREATE FUNCTION dbo.RemoveMSpaces (@Txt VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
IF CHARINDEX(‘ ‘,@Txt) = 0
RETURN @Txt;
RETURN dbo.RemoveMSpaces(REPLACE(@Txt, ‘ ‘, ‘ ‘));
END
GO
****************END Function***********************
SELECT REPLACE(RTRIM(firstname), ‘ ‘, ‘ ‘) AS MultipleSpace from emp where idx=1300
SELECT dbo.RemoveMSpaces(firstname) ASRemoveMultipleSpace
From emp where idx=1300
Result
Oracle
Select Replace(replace(replace(replace(replace(data,
‘ ‘,’ ‘),’ ‘,’ ‘),’ ‘,’ ‘),’ ‘,’ ‘),’ ‘,’ ‘) as RemoveMultipleSpace,
length(data) from (select ’22 Main Steet Roma’ data from dual )
Result
