# Replace multiple spaces with one space

• ### Question

• i have searched for other theads on this but not been able to make them work. i have several fields that have multiple spaces betweent the City State and Zip Code. i want to be able to make only one space between each.  a combination of Substring and Replace is what i have been trying but not able to make it work. a do while might be what i need but not sure how to do it.

Thursday, 10 September 2015 9:29 PM

### All replies

• Take a look at this post

http://www.sqlservercentral.com/Forums/Topic819042-203-15.aspx#bm821209

I knew I had to google on 'replace multiple space Jeff Moden' and I am glad I did as apparently new nice solution was developed.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

Thursday, 10 September 2015 10:00 PM

Kalman Toth Database & OLAP Architect Artificial Intelligence
New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014

Friday, 11 September 2015 2:49 AM
• Thanks for the response.  The code is a bit too complex for me as a beginner to figure out.  I am not sure what to do with functions so i might end up doing multiple replaces until the double spaces are gone.

Jim

Friday, 11 September 2015 3:59 PM
• Here's a simple way:

```while 1=1
begin
update T set col = replace(col,'  ',' ')
if @@rowcount = 0 break
end```

David

Friday, 11 September 2015 4:05 PM
• Thanks David, that is simple.  I will try that one out.  In the meantime, i got something to work.

```SELECT

CASE
THEN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TXTAD4_TXAAD4,'  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' ')
ELSE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TXTAD4_TXAAD4,'  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' ')

,TXPRCL
FROM         PCWEBF21_VIEW1_ALTERNATE_TRIM

Order By TXPRCL
```

thanks for the other responses also.

Jim

Jim

• Marked as answer by Friday, 11 September 2015 8:17 PM
• Unmarked as answer by Friday, 11 September 2015 9:09 PM
Friday, 11 September 2015 5:10 PM
• Use a function:

```CREATE FUNCTION dbo.fn_RemoveMultipleSpaces (
@inputString varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
WHILE CHARINDEX(SPACE(2), @inputString) > 0
BEGIN
SET @inputString = REPLACE(@inputString, SPACE(2), SPACE(1))
END

RETURN @inputString
END```

A Fan of SSIS, SSRS and SSAS

Friday, 11 September 2015 5:21 PM
• thanks all, i will try respond to all proposals at once.  i got ideas from every post and they appear more elegant and above the level i am at in T-SQL.  So i will mark this done.

Jim

• Marked as answer by Friday, 11 September 2015 9:24 PM
• Unmarked as answer by Friday, 11 September 2015 9:34 PM
Friday, 11 September 2015 9:24 PM
• Friday, 17 January 2020 4:07 PM