ltrim and whitespaces

  • i have imported data into sql using a comma delimited file but for some reason when i use the ltrim function nothing happens. Could this be because the whitespaces are being viewed as characters. if so how can i change this.

     any help much appreciated

    C.M

  • You can find out if they are spaces or characters that look like spaces (assuming that all the rows for a given column have leading spaces)...

    SELECT ACSII(LEFT(columnname,1))

    FROM yourtable

    If the come back with a 32, they are spaces and you're doing something wrong with LTRIM.

    If they don't come back as 32, write down the number and substitute it in the following to change the abhorant spaces to real spaces...

    --===== Change the wierd character to spaces

    UPDATE yourtable

         SET columnname = REPLACE(columnname,CHAR(thenumber),' ')

    It would be helpful if you posted the DDL for the table as well as some copy/paste data if none of the above works.

     

    --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)

  • Forgive me if I have misunderstood but ltrim trims leading spaces.  If you wish to trim trailing spaces, you should use rtrim or indeed trim which will truncate leading and trailing spaces.

    regards

     

    Shaun

    Quis custodiet ipsos custodes.

  • Yep... that too.  For the record, there is no TRIM function in SQL Server 7 or 2000... just LTRIM and RTRIM.  Dunno about 2005.

    --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)

  • Nope, no trim in 2005 either, just ltrim and rtrim

    /Kenneth

  • thanks Jeff. the select statement returned 9 which i believe means tab character. Strange because the text file wasnt tab delimited as far as i can see. The replace function ended up doing the trimming for me.

    thanks for the help

  • You bet... and to confirm, yes, CHAR(9) is the tab character.  You might be able to simplify things if you used the tabl character as a delimiter in the import but I haven't seen the data so dunno for sure...

    --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)

  • Just to clarify, it is SSIS that supports the TRIM() function

    http://msdn2.microsoft.com/en-us/library/ms139947.aspx

    It wouldn't have worked in this case though because it only recognises spaces.

    My apologies

    Shaun

    Quis custodiet ipsos custodes.

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

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