none
Sql Server Migration Assistant - Convert CHAR column value not padding

    Question

  • Hi,

    I have done Migration form MySql to MS Sql 2005 but having issue with CHAR Columns.

    When i check the value on MS Sql table for CHAR(3) column in and it not padding off.

    Ex.

    Column Data type = CHAR(3)

    Value inserting: 'MS' but when i query then it shows as 'MS ', it shows blank space and i know that sql works like that way as it 's not trimming the blank space.

    I have few columns like CHAR and I don't need to shows values as blank space.

    How can handle this one in SSMA?

    I tried to set database level also '

    SET ANSI_PADDING OFF but it didn't work.

    Thanks,

    Friday, July 19, 2013 4:57 PM

Answers

  • Hi poratips,

    Microsoft SQL Server works like that way as it will not trim the blank space while the actual length is smaller than the defined length.

    But we can easily use LTRIM() and RTRIM() together and simulate TRIM() functionality.

    i.e.  SELECT LTRIM(RTRIM(columnName)) FROM tableName

    Regarding to ANSI_PADDING, it affects only the definition of new columns. After the column is created, SQL Server stores the values based on the setting when the column was created. Existing columns are not affected by a later change to this setting. That’s why it doesn’t work after you set it off.

    Thanks,
    Candy Zhou

    Monday, July 22, 2013 8:39 AM

All replies

  • Hi poratips,

    Microsoft SQL Server works like that way as it will not trim the blank space while the actual length is smaller than the defined length.

    But we can easily use LTRIM() and RTRIM() together and simulate TRIM() functionality.

    i.e.  SELECT LTRIM(RTRIM(columnName)) FROM tableName

    Regarding to ANSI_PADDING, it affects only the definition of new columns. After the column is created, SQL Server stores the values based on the setting when the column was created. Existing columns are not affected by a later change to this setting. That’s why it doesn’t work after you set it off.

    Thanks,
    Candy Zhou

    Monday, July 22, 2013 8:39 AM
  • Thanks Candy.

    Right, Due to our application using all the time these CHAr Fields so i don't not prefer to use function due to performance issue which think but you can correct me.

    I am trying to looking into SSMA to make project level change to modify dataytype setting from CAHR to VARCHAR but not much success.

    I think i also tried ANSI_PADDING settings before I start the migration but didn't work.

    Thansk

    Friday, July 26, 2013 6:41 PM