ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

  • Well that's just weird. I just posted on one of the 2-year-old threads referenced in this article earlier this morning, before I saw that this article had been republished. Perhaps now would be a good time to buy a Lotto ticket?

  • Nice article. I too had some of the same misconceptions about padding as you.

  • tony.sawyer (7/16/2010)

    The ANSI_PADDING affects the CHAR types too - surprised the hell out of me when I finally discovered why my tables weren't linking!

    doing a simple select '<' + prod_code + '>' from ... showed it to only contain 7 characters with no trailing white space.

    select '<' + cast(prod_code as char(10)) + '>' from ... showed up the trailing 3 spaces (and then allowed EF4 to link the data to another table that had ANSI_PADDING set to ON)

    We're using SQL Server 2005 here - give it a shot and see if you get the same results


    You are correct. The difference is that unicode (nchar/nvarchar) always behave with ANSI_PADDING ON settings.

  • To brighten the path for others following this later (like me), I wanted to spell out more about what this means for a Lookup in SSIS (according to the package I just got working). SSIS includes any trailing spaces in a lookup, and when matching to the input source, 'a' != 'a ' (space).

    My source was Excel, and I didn't find any data conversion that would add the trailing spaces so that 'a' coming in would be 'a ', so I changed my lookup from a table/view selection to a SQL statement that trimmed my db-side fields to that 'a ' was changed to 'a', thus matching the 'a' coming from Excel.

    Adding the trim is easy enough, but I'm puzzled why SSIS won't allow a data conversion to a type that will behave like char(10) and automatically add trailing spaces (if I just missed it, please explain how to do that in SSIS).

  • I believe, and I'm not an SSIS expert, that SSIS is doing the compare within SSIS, meaning that .NET comparison rules apply. You need to match case AND trim any trailing spaces. I also believe that an Excel source uses Unicode data types for any strings, so SQL Server would pad with trailing spaces.

Viewing 5 posts - 46 through 49 (of 49 total)

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