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

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

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

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

Keep it simple :-)