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