DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to remove multiple spaces from a string?

Execute the following Microsoft SQL Server T-SQL scripts in Management Studio Query Editor to demonstrate space removal in middle of string. LTRIM and RTRIM remove leading and trailing spaces.

-- Detect special non-printing characters (white space) the following way

DECLARE @WhiteSpace varchar(64) = 'New York'+' '+char(9)+'City'

SELECT ExposeWP=CONVERT(varbinary(max), @WhiteSpace), NonPrint=@WhiteSpace

/* ExposeWP NonPrint

0x4E657720596F726B200943697479      New York  City  */

-- Hex 20 is space, and 09 is printing as space but it is a special character

------------

-- SQL remove unwanted spaces from text - delete extra spaces from string
DECLARE @text varchar(256)
SELECT @text = 'SQL   Server    2008   Enterprise    Edition'
SELECT len(@text)
-- 44
SELECT @text=REPLACE(@text, '  ', ' ') -- replace 2 spaces with 1 space
SELECT len(@text)
-- 38
SELECT @text=REPLACE(@text, '  ', ' ') -- replace 2 spaces with 1 space
SELECT len(@text)
-- 34
SELECT @text=REPLACE(@text, '  ', ' ') -- replace 2 spaces with 1 space
SELECT len(@text)
-- 34
SELECT @text
--SQL Server 2008 Enterprise Edition

------------

-- SQL remove extra multiple spaces in string - SQL user defined function - UDF

-- SQL scalar string function - SQL charindex - SQL replace string function

USE AdventureWorks;

GO

CREATE FUNCTION fnRemoveMultipleSpaces

               (@InputString VARCHAR(1024))

RETURNS VARCHAR(1024)

AS

  BEGIN

    WHILE CHARINDEX('  ',@InputString) >-- Checking for double spaces

      SET @InputString =

        REPLACE(@InputString,'  ',' ') -- Replace 2 spaces with 1 space

    

    RETURN @InputString

  END

GO

-- Test UDF

DECLARE @Text varchar(256)

SET @Text = 'The  fox   ran into the forest   following         other   foxes!'

SELECT Original = @Text,

       SpacesRemoved = dbo.fnRemoveMultipleSpaces (@Text)

GO

 

/* Results

 

Original   

The  fox   ran into the forest   following         other   foxes!

SpacesRemoved

The fox ran into the forest following other foxes!

*/

 

Related articles:

SQL Server Transact-SQL (SSQA.net) : Squeeze function

REPLACE (Transact-SQL)

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE