locked
Removing unwanted spaces in columns RRS feed

  • Question

  • How to remove unwanted spaces empty lines in table columns
    • Changed type KJian_ Friday, October 15, 2010 2:03 AM
    Thursday, October 14, 2010 7:32 AM

Answers

  • I think you need to define "unwanted spaces" .

    The code will remove all spaces. For example, ' My Settings ' will remain ' My Settings '. But I personally feel, it should be 'My Settings' (without space at the end and at the begining)

    you also need to have LTRIM and RTRIM

    Select rtrim(ltrim(replace(REPLACE(REPLACE(REPLACE(' My Settings ', CHAR(10), ''), CHAR(13), ''), CHAR(9), ''),' ','')))
    
    

    Please visit my Blog for some easy and often used t-sql scripts
    • Marked as answer by Kalman Toth Tuesday, October 19, 2010 12:09 PM
    Thursday, October 14, 2010 11:15 AM

All replies

  • I used this

    replace(REPLACE(REPLACE(REPLACE(@inputstring, CHAR(10), ''), CHAR(13), ''), CHAR(9), ''),'  ','')

     

    is this ok ?

    Thursday, October 14, 2010 7:32 AM
  • I used this

    replace(REPLACE(REPLACE(REPLACE(@inputstring,CHAR(10),''), CHAR(13), ''), CHAR(9),''),'  ','')

     

    is this ok ?


    Yes, that's the way to go.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Thursday, October 14, 2010 7:48 AM
  • I think you need to define "unwanted spaces" .

    The code will remove all spaces. For example, ' My Settings ' will remain ' My Settings '. But I personally feel, it should be 'My Settings' (without space at the end and at the begining)

    you also need to have LTRIM and RTRIM

    Select rtrim(ltrim(replace(REPLACE(REPLACE(REPLACE(' My Settings ', CHAR(10), ''), CHAR(13), ''), CHAR(9), ''),' ','')))
    
    

    Please visit my Blog for some easy and often used t-sql scripts
    • Marked as answer by Kalman Toth Tuesday, October 19, 2010 12:09 PM
    Thursday, October 14, 2010 11:15 AM
  • If you wanted to remove all spaces and CRL, LF characters, then it's OK. But if you only wanted to reduce spaces (say, double space to a single space), then this code is not OK.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, October 14, 2010 12:14 PM