We have already seen three easy methods to do the same in SQL Server by using REPLACE keyword in the post -‘ To Remove Multiple and Duplicate Spaces to Single Space in SQL Server ‘. In this current blog post, we would see how to remove Multiple Spaces to Single Space WITHOUT USING REPLACE keyword in SQL Server like below.
METHOD – using XML
DECLARE @TestTable AS TABLE(input VARCHAR(MAX)); INSERT INTO @TestTable VALUES ('HAPPY NEWYEAR 2020'), ('WELCOME ALL !'); SELECT CAST('<r><![CDATA[' + input + ']]></r>' AS XML).value('(/r/text()) cast as xs:token?','VARCHAR(MAX)') AS Expected_Result FROM @TestTable; --OUTPUT /* Expected_Result HAPPY NEWYEAR 2020 WELCOME ALL ! */
Image Pic with Expected Result
Thus, we can use XML Method like above to remove multiple spaces to single space and get the same expected result without using REPLACE keyword. The Special Thanks to my friend,XML Expert ‘ Yitzhak Khabinsky ‘ . The above method is unit tested in both Azure SQL db and on-premises.