Replace multiple spaces with one space

Tüm Yanıtlar

• 

  

  0

  Oylamak İçin Oturum Aç

  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.

  ---

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

  • Yanıt Olarak Öneren Lydia ZhangMicrosoft contingent staff 11 Eylül 2015 Cuma 06:52
  • Yanıt Olarak İşaretleyen Lydia ZhangMicrosoft contingent staff 18 Eylül 2015 Cuma 06:44

  10 Eylül 2015 Perşembe 22:00
• 

  

  0

  Oylamak İçin Oturum Aç

  See also:

  How to remove multiple spaces from a string?

  ---

  ---

  ---

  ---

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

  
  

  
  

  • Yanıt Olarak Öneren Lydia ZhangMicrosoft contingent staff 11 Eylül 2015 Cuma 06:52
  • Yanıt Olarak İşaretleyen Lydia ZhangMicrosoft contingent staff 18 Eylül 2015 Cuma 06:44

  11 Eylül 2015 Cuma 02:49
• 

  

  0

  Oylamak İçin Oturum Aç

  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

  11 Eylül 2015 Cuma 15:59
• 

  

  0

  Oylamak İçin Oturum Aç

  Here's a simple way:

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

  
  

  David

  ---

  David http://blogs.msdn.com/b/dbrowne/

  • Yanıt Olarak İşaretleyen Lydia ZhangMicrosoft contingent staff 18 Eylül 2015 Cuma 06:44

  11 Eylül 2015 Cuma 16:05
• 

  

  0

  Oylamak İçin Oturum Aç

  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

  ---

  Jim

  • Yanıt Olarak İşaretleyen GIS Jim 11 Eylül 2015 Cuma 20:17
  • Yanıt İşaretini Geri Alan GIS Jim 11 Eylül 2015 Cuma 21:09

  11 Eylül 2015 Cuma 17:10
• 

  

  0

  Oylamak İçin Oturum Aç

  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

  • Yanıt Olarak İşaretleyen Lydia ZhangMicrosoft contingent staff 18 Eylül 2015 Cuma 06:44

  11 Eylül 2015 Cuma 17:21

  Yanıtlayıcı
• 

  

  0

  Oylamak İçin Oturum Aç

  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

  • Yanıt Olarak İşaretleyen GIS Jim 11 Eylül 2015 Cuma 21:24
  • Yanıt İşaretini Geri Alan Naomi N 11 Eylül 2015 Cuma 21:34

  11 Eylül 2015 Cuma 21:24
• 

  

  0

  Oylamak İçin Oturum Aç

  Check this blogpost of mine-

  https://arulmouzhi.wordpress.com/2019/11/16/to-remove-multiple-and-duplicate-spaces-to-single-space-in-sql-server/

  17 Ocak 2020 Cuma 16:07