How to remove blank spaces

  • In Company table column name "CompanyInfo" varchar(750).

    In this column company information is stored, but end of this string some blank spaces are added.

    I tried to remove blank spaces using RTRIM(CompanyInfo) or REPLACE(CompanyInfo,' ','').

    It removed blank spaces, but end of the string one blank space remain same, if I use again RTRIM or REPLACE it won't go.

    before trim CompanyInfo='This is the description of company '

    after trim CompanyInfo='This is the description of company '

    Can anyone please tell me, what is the problem and how to remove blank spaces.

    Thanks,

    JS

  • Can u check the ascii code of the last character?

    select ascii(right(companyinfo,1)) from company

    if its 32, it's a space and should be removed using rtrim

    if its something else, you need to replace that character using replace ()function

    REPLACE(CompanyInfo,CHAR(use the ascii value here),'')



    Pradeep Singh

  • You may want to view this page

    http://msdn.microsoft.com/en-us/library/218s85f8.aspx

    to see a list of non-printable characters

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • I got an answer after researching.

    It was problem with \t,,\r

    It used this statement to remove blank spaces.

    LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(CompanyInfo, CHAR(13), ''), CHAR(10), ''), CHAR(9), '')))

  • Cool... thanks for posting what you ended up with.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hi All,

    How to remove database unallocated space in the server.

    After deleted the unwanted tables in the database we got 40gb unallocated space in the drive.

    Thanks

    G Arunagiri

  • gkarung (9/30/2010)


    Hi All,

    How to remove database unallocated space in the server.

    After deleted the unwanted tables in the database we got 40gb unallocated space in the drive.

    Thanks

    G Arunagiri

    It may seem like a silly question but how much allocated space remains in that database?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply