T-SQL: UDF Scalar Function to Clean Multiple Spaces

1 Apr, 2017  in T-SQL  tagged String / T-SQL / User Defined Function by Peter Lalovsky

---

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

Create database

Transact-SQL

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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

Transact-SQL

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

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

Transact-SQL

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

1 2 3 4 5 6 7 8

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

Transact-SQL

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

1 2 3 4 5 6 7 8 9

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 :-)

---