Post reply

Removing trailing spaces in a table column

  • January 22, 2008 at 2:43 am

    #73958

    Hi

    I am having trouble removing trailing spaces in a table column (query phrase).

    so far i have tried using Ltrim , rtrim but no luck , i have used len and substring as well . funny thing is when using len(queryphrase) its counting the trailing space.

    also an update with replace dosen't work

    I have set the table with ansi padding off and have used both nvarchar and char columns to see if it made any difference , but no luck there either.

    Are there any other options available ???

    Thanks in advance

    🙂

    Jayanth Kurup[/url]

  • January 22, 2008 at 2:53 am

    #770389

    Check the following link may be useful

    http://www.thescripts.com/forum/thread162850.html

  • January 22, 2008 at 2:56 am

    #770392

    LEN() function is NOT counting trailing spaces.

    DATALENGTH() does.

    Are you the spaces really are spaces (ascii value 32)?

    Make sure they are not hard-spaces (ascii value 160).

    N 56°04'39.16"
    E 12°55'05.25"

  • January 22, 2008 at 4:20 am

    #770413

    Hi ,

    Fixed the issue 🙂 , the trailing space was in fact a carriage return , ascci value (13) , used a replace(queryphrase,char(13),'') to fix the issue .

    Guess where i went wrong was assuming there could only be spaces in the column.

    Jay

    Jayanth Kurup[/url]

  • June 14, 2011 at 9:44 am

    #1338483

    Can you advise as to how you fixed the issue and how you knew it was a CR/LF. We are having the same issue when loading mainframe data into our systems and an RTRIM is not working. Thanks.

  • June 14, 2011 at 10:05 am

    #1338504

    SELECT ASCII(SUBSTRING(Col1, n, 1))

    N 56°04'39.16"
    E 12°55'05.25"

  • October 3, 2012 at 12:49 pm

    #1545160

    I used following to remove only trailing white spaces in a CHAR(30) column:

    RTRIM(LTRIM(CAST(City_Id As VARCHAR(30)))) AS City_Id

    Piyush Varma

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

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