T-SQL: UDF Scalar Function to Clean Multiple Spaces

While working on autocomplete dropdown, i created a simple scalar function that replaces multiple spaces with one.

Create database

USE [master];
GO

IF (DB_ID('PL') IS NOT NULL)
	BEGIN
		ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
		DROP DATABASE PL;
	END
GO

CREATE DATABASE PL;
GO

USE PL;
GO

USE [master];

IF (DB_ID('PL') IS NOT NULL)

BEGIN

ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP DATABASE PL;

END

CREATE DATABASE PL;

USE PL;

Create Function

USE PL;
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:			Peter Lalovsky
-- Create date: 2017-04-01
-- Description:	Replace more than one space with one space
-- =============================================

CREATE FUNCTION dbo.udf_CleanSpaces (@String VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
	DECLARE @ResultVar VARCHAR(MAX);

	SELECT @ResultVar = 
		LTRIM(RTRIM(
			REPLACE(
				REPLACE(
					REPLACE(@String, '  ', ' ' + CHAR(1))
				, CHAR(1) + ' ', '')
			, CHAR(1), '')
		));

	RETURN @ResultVar;

END
GO

USE PL;

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: Peter Lalovsky

-- Create date: 2017-04-01

-- Description: Replace more than one space with one space

-- =============================================

CREATE FUNCTION dbo.udf_CleanSpaces (@String VARCHAR(MAX))

RETURNS VARCHAR(MAX)

BEGIN

DECLARE @ResultVar VARCHAR(MAX);

SELECT @ResultVar =

LTRIM(RTRIM(

REPLACE(

REPLACE(@String, ' ', ' ' + CHAR(1))

, CHAR(1) + ' ', '')

, CHAR(1), '')

));

RETURN @ResultVar;

END

Test the function

USE PL;
GO

DECLARE @String VARCHAR(128) = ' 123         abc                789 def ';
SELECT
	'>' + @String + '<' AS NotCleaned
	, '>' + dbo.udf_CleanSpaces(@String) + '<' AS Cleaned;
GO

USE PL;

DECLARE @String VARCHAR(128) = ' 123 abc 789 def ';

SELECT

'>' + @String + '<' AS NotCleaned

, '>' + dbo.udf_CleanSpaces(@String) + '<' AS Cleaned;

If you need to clean only the non-leading and the non-trailing spaces (the inner spaces), remove LTRIM(RTRIM(()) (lines 23, 29)

Delete database

USE [master];
GO

IF (DB_ID('PL') IS NOT NULL)
	BEGIN
		ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
		DROP DATABASE PL;
	END
GO

USE [master];

IF (DB_ID('PL') IS NOT NULL)

BEGIN

ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP DATABASE PL;

END

Keep it simple :-)