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.
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.
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.
Here's a simple way:
while 1=1 begin update T set col = replace(col,' ',' ') if @@rowcount = 0 break end
David
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
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