locked
Converting char to varchar RRS feed

  • Question

  • Hi!

    I'm upgrading somewhat big VB6 project to VB.Net project.
    On the existing database design all of the charactor column was defined with fixed length char datatype, since I'm using tableadapter with DataSet. I'd lilke to convert current column type to varchar so I don't have to trim the data and I can use dataset's change tracking. I'm using VS2008 Team Database edition. What would be the simplest and fastest way to convert column's char datatype to varchar. I can use database project to change column type easily but I also found out that I still have to trim the existing column data.

    Thanks,

    Jay
    Tuesday, January 26, 2010 12:35 AM

Answers

  • Thanks for the reply.

    I don't understand what you mean.

    I have over 100 table with each table defined column datatype char. And I want to convert that datatype to varchar.
    For example;

    Create table Customer(
     ID int primary key,
     name char(40) NOT NULL,
     phone char(20) NOT NULL,
     fax char(20) NOT NULL
    )

    to Customer table with name, phone, fax column datatype to varchar. Table has rows in them with fixed length in char column.
    I can column data type using VSTS Database edition's Database project and compare schema to change column type easily.
    But I still have char column(in this example name, phone, fax column) with fixed length data(padding space at the end as char length) so when I load dataset with tableadapter's command(select id,name,phone,fax from Customer) Dataset's column data will have padding space at the end.

    Thanks,

    Jay
    • Marked as answer by Jay Chung Tuesday, January 26, 2010 3:45 PM
    Tuesday, January 26, 2010 6:27 AM

All replies

  • Hi Jay

    Please check out the schema of the Table.
    It sounds that the cause was the length of the CHAR type is longer than the VARCHAR's.

    If for example a CHAR(10) column changed to VARCHAR(20) type, it won't trim the existing column data.

    Thanks.

    Figo Fei
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, January 26, 2010 5:52 AM
  • Thanks for the reply.

    I don't understand what you mean.

    I have over 100 table with each table defined column datatype char. And I want to convert that datatype to varchar.
    For example;

    Create table Customer(
     ID int primary key,
     name char(40) NOT NULL,
     phone char(20) NOT NULL,
     fax char(20) NOT NULL
    )

    to Customer table with name, phone, fax column datatype to varchar. Table has rows in them with fixed length in char column.
    I can column data type using VSTS Database edition's Database project and compare schema to change column type easily.
    But I still have char column(in this example name, phone, fax column) with fixed length data(padding space at the end as char length) so when I load dataset with tableadapter's command(select id,name,phone,fax from Customer) Dataset's column data will have padding space at the end.

    Thanks,

    Jay
    • Marked as answer by Jay Chung Tuesday, January 26, 2010 3:45 PM
    Tuesday, January 26, 2010 6:27 AM
  • I've kind of found the solution;

    select 'update ' + o.name + ' set ' + c.name + '=RTRIM(' + c.name + ')'    
    from syscolumns c, sysobjects o
    where o.id=c.id and c.xtype=167 and o.type='U'
    order by o.name,c.name

    Use above sql to generate update statement for every converted varchar column and update with trimmed value.

    It is really only solution? Is there any other better way using VSTS Database Edition?

    Thanks,

    Jay
    Tuesday, January 26, 2010 9:24 AM
  • Hi Jay

    As far as I know, we need to remove the trailing space by sql script, data pro doesn't have such a facility to do the trimming, it can do schema changes instead.

    Thanks.

    Figo Fei
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, January 27, 2010 3:40 AM
  • Hi! Figo,

    Thanks for your reply. I was hoping some faster, easier way to trim it, since I'm pretty new to the Database edition.

    Thanks,

    Jay

    Wednesday, January 27, 2010 2:06 PM
  • Hi Jay

    One thing you could try is to put the script and execution in the post deployment event.
    To do this we need to edit the PostDeployEvent element in the .dbproj file. you can see detailed instructions from http://blogs.msdn.com/gertd/archive/2009/10/13/pre-and-post-deployment-events.aspx

    Hope it help.

    Thanks.

    Figo Fei
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, January 28, 2010 1:30 AM