Replace multiple spaces with one space

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.

September 10th, 2015 5:31pm

Take a look at this post

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

referenced from this article

http://www.sqlservercentral.com/articles/T-SQL/68378/

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.

Free Windows Admin Tool Kit Click here and download it now
September 10th, 2015 6:02pm

See also:

How to remove multiple spaces from a string?

September 10th, 2015 10:51pm

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.

Free Windows Admin Tool Kit Click here and download it now
September 11th, 2015 12:04pm

Here's a simple way:

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

David

September 11th, 2015 12:10pm

Thanks David, that is simple.  I will try that one out.  In the meantime, i got something to work.

SELECT

  CASE
    WHEN CHARINDEX(' ', TXTAD4_TXAAD4) >0
      THEN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TXTAD4_TXAAD4,'  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' ')
      ELSE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TXTAD4_TXAAD4,'  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' ')
  END As TXTAD4_TXAAD4

      ,TXPRCL
FROM         PCWEBF21_VIEW1_ALTERNATE_TRIM

Order By TXPRCL

thanks for the other responses also.

Jim

Free Windows Admin Tool Kit Click here and download it now
September 11th, 2015 1:15pm

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

September 11th, 2015 1:26pm

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.
Free Windows Admin Tool Kit Click here and download it now
September 11th, 2015 5:29pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics