How to remove duplicate space from the string using T-SQL

Question

I have a string like this ' Hello <> world '. I am using below code to remove duplicate space from the string. But it removes <> from the string.

SELECT ' Hello  <>   world ' AS Name
INTO #Temp;

SELECT REPLACE(REPLACE(REPLACE(RTRIM(LTRIM(Name)), ' ', '<>'), '><', ''), '<>', ' ') AS CleanName
FROM #Temp;

Is there any alternative?

@SRM : not a duplicate, the question you are referring removes ALL spaces, while the OP wants to retain non-duplicated spaces in the middle of the string — GMB, Dec 18 '18 at 23:15

score 2 · Accepted Answer · answered Dec 18 '18 at 23:12

2

Use this expression instead, that relies on non-printable ASCII characters :

SELECT REPLACE(REPLACE(REPLACE(RTRIM(LTRIM(Name)),' ','CHAR(17)CHAR(18)'),'CHAR(18)CHAR(17)',''),'CHAR(17)CHAR(18)',' ') AS CleanName
FROM #Temp;

Yields :

Hello <> world

answered Dec 18 '18 at 23:12

GMB

216,147
25
84
135

score 1 · Answer 2 · answered Dec 19 '18 at 00:54

1

Maybe this could help you:

declare @Name varchar(200)
select @Name = ' Hello  <>   world '
while charindex('  ',@Name)>0 select @Name=REPLACE(@Name,'  ',' ')
select @Name

answered Dec 19 '18 at 00:54

Xabi

465
5
8

score 0 · Answer 3 · answered Dec 18 '18 at 23:10

The technique is solid, but if you want to keep <>, then you can use a rare text pattern

Example

SELECT ltrim(rtrim(replace(replace(replace(Name,' ','†‡'),'‡†',''),'†‡',' ')))
FROM #Temp;

Returns

Hello <> world

Note: the ltrim(rtrim(...)) is optional

How to remove duplicate space from the string using T-SQL

3 Answers3