Remove Multiple Spaces using T-SQL XML Functions - Replace Multiple Spaces with Single Space
String manupulation is very frequent in programming and also in t-sql sql development.
T-SQL developers and SQL DBA 's frequently require to remove multiple spaces and replace multiple spaces with single space in string variables in sql data types like varchar or nvarchar.
I have developed a sql script which can be used in order to replace multiple spaces within a string in sql.
Here is the t-sql script to remove extra spaces in sql variable @str which is in SQL Server data type nvarchar(max).
In this script the sql code replace multiple spaces with single space.
DECLARE @str NVARCHAR(MAX)
DECLARE @xml XML
SET @str = N' remove extra spaces replace multiple spaces excess spaces '
SELECT @str = REPLACE('<r>' + @str + '</r>', SPACE(1), '</r><r>')
SELECT @xml = CAST(@str AS XML);
WITH CTE AS (
SELECT
val
FROM (
SELECT
i.value('.','nvarchar(100)') val
FROM @xml.nodes('/r') AS x(i)
) p
WHERE
val <> ''
)
SELECT @str =
LTRIM(RTRIM((
SELECT SPACE(1) + val FROM CTE FOR XML PATH('')
)))
SELECT @str
As you see while the input of the above t-sql script is :
' remove extra spaces replace multiple spaces excess spaces '
the output of the sql code is as :
'remove extra spaces replace multiple spaces excess spaces'
We delete the extra spaces in the sql code line containing "val <> ''".
This where condition eliminated the spaces from the string sql variable @str which is converted into XML data type in variable @xml and then converted into rows using the FROM @xml.nodes() method.
The second task to remove extra spaces from character variable is concatenating the parsed words with a single delimiter value space(1) (' ').
For string concatenation I code in sql using the FOR XML PATH() method. And while concatenating I used the SPACE character as the delimiter between words.
Actually, I did not test the performance issues related with the execution of the above sql batch script.
You should test the above remove multiple spaces script with load according to your needs and decide to use this method to replace the multiple spaces in your string variables.
Now we can convert above SQL code that can be used for replacing repeating space characters within a given string into a user-defined SQL function as follows:
CREATE OR ALTER FUNCTION ReplaceMultipleSpaces (
@str NVARCHAR(MAX)
) RETURNS NVARCHAR(MAX)
BEGIN
DECLARE @xml XML
SELECT @str = REPLACE('<r>' + @str + '</r>', SPACE(1), '</r><r>')
SELECT @xml = CAST(@str AS XML);
;WITH CTE AS (
SELECT
val
FROM (
SELECT
i.value('.','nvarchar(100)') val
FROM @xml.nodes('/r') AS x(i)
) p
WHERE
val <> ''
)
SELECT @str =
LTRIM(RTRIM((
SELECT SPACE(1) + val FROM CTE FOR XML PATH('')
)))
RETURN @str
END
GO
If as SQL programmer, you have created ReplaceMultipleSpaces() function in your SQL Server database, you can use this SQL function as follows in your code
DECLARE @str NVARCHAR(MAX)
SET @str = N' Hello! My name is Eralper '
SELECT dbo.ReplaceMultipleSpaces(@str)
When the database developers execute above sample SQL script, output will show how multiple spaces within given string variable are replaced with single space characters.
