Alternatives · Basic Logics

Replacing Multiple Spaces with Single Space WITHOUT Using REPLACE in SQL Server

Introduction

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

SQL SCRIPT

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())[1] cast as xs:token?','VARCHAR(MAX)')
	AS Expected_Result
FROM @TestTable;

--OUTPUT
/*
Expected_Result
HAPPY NEWYEAR 2020
WELCOME ALL !
*/

Image Pic with Expected Result

CONCLUSION

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.

Thanks for reading!

FOLLOW BLOG for Interesting Upcoming Posts!

Advertisement

5 thoughts on “Replacing Multiple Spaces with Single Space WITHOUT Using REPLACE in SQL Server

  1. Why?!? Did you checked the Execution Plan, IO, CPU, or Time? Each of these will scream the question “why?”. SQL 2019, EP shows multiple expensive actions like using “table spool” and Nested loop twice, and more… What is the goal in not using a build-in function like REPLACE?

    Like

    1. Hi, Actually many of us will think and use REPLACE for this scenario. The goal for this post is let the users to know, we can do the same without the help of REPLACE too. Haven’t checked EP, IO etc., yet. Already posted the actually using REPLACE methods in my previous blogposts. Will surely analyze on perf among various methods behind this and let you know!

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s