Home Forums Article Discussions Article Discussions by Author Discuss content posted by Jack Corbett ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum RE: ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

  • July 31, 2009 at 10:46 am

    #1033068

    Matt Campbell (7/31/2009)

    I take no chances when it comes to SQL Server and trailing spaces. I always use LTRIM(RTRIM(col_name))when selecting data or updating data if the field is any kind of string-holder, and I do so on left- and right-hand side comparison clauses too. Basically, anywhere I refer to a table field that is a string container, it always gets this kind of treatment. It adds overhead of course to the query but unless there is a critical timing issue (and there oughtn't be if you wrote the app right), using this "Kill 'em all let God sort 'em out" approach has never failed me.

    I also always Trim() string values from ADO recordset fields to be doubly-sure. Just because I am paranoid doesn't mean I'm not right! 🙂

    Heh... and as Jack points out, that pretty much eliminates any chance at real peformance if the proper indexes are available. I'd suggest a different approach in the future.

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