Post reply

Trim Functions Not Working Properly

  • June 20, 2006 at 9:48 am

    #115557

    I imported an Excel spreadsheet into the database in its own table. I changed to column data type to varchar(255) and I am trying to use rtrim and ltrim to trim off the spaces before and after the names, but it is not working....does anyone know why and/or have any advice on how to remedy this problem?

     

    Thanks,

    Michael

  • June 20, 2006 at 10:25 am

    #644871

    See if this gives better results. You probably have whitespace characters other than space.

    select

    rtrim(ltrim(replace(replace(replace(colname,char(9),' '),char(10),' '),char(13),' ')))

    from

    yourtable

    I hate Excel. I have seen it go though a column of dates and interpret those with day <= 12 as if they were in default format, and those with day > 12 (i.e. couldnt be treated as in default format) as though they were in the alternative format.

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

  • June 20, 2006 at 10:26 am

    #644872

    Hi Michel,

    I am using the following Query : but it is executing properly.

     

    select ltrim(rtrim(convert(varchar(255),

    '           gfbdfrfrf                 dfjdfhjdfh      kjfdkf           ')))

     

    Regards,

    Amit Gupta

     

     

  • June 20, 2006 at 10:34 am

    #644874

    Amit, how does that help?

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

  • June 20, 2006 at 11:20 am

    #644891

    It did not work...unfortunately. I have no idea what is going on...but I am getting angry quick...I guess I will try substrings...

     

    Thanks anyway

  • June 20, 2006 at 11:28 am

    #644893

    can you post results of:

    select distinct ascii(substring(yourcol,1,1))

    from yourtable

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

  • June 20, 2006 at 11:35 am

    #644895

    160

  • June 20, 2006 at 12:01 pm

    #644902

    That's the ASCII for a blank that we often use when we don't want spaces trimmed for various reasons. Try:

    SELECT Replace(colname, Char(160),'')

    ...and if you still have problems, then you also have actual spaces, so you can wrap the above with trim functions as needed.

  • July 28, 2009 at 1:54 am

    #1030887

    🙂

    I have learned something today!

    Thanks folks

  • December 22, 2009 at 3:13 am

    #1095306

    That Worked for me too.. Thanks a Lot

Viewing 10 posts - 1 through 9 (of 9 total)

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