While working on autocomplete dropdown, i created a simple scalar function that replaces multiple spaces with one.
Create database
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
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
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
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 :-)