char & varchar

Reply

SpoonHP Posted: 08-17-2006 9:38 PM

rated by 0 users

Hi,

I have problem moving data from one table to another table with different data type:

table1:

areacode varchar(50)

phone varchar(50)

table2:

areacode char(10)

phone char(15)

After moving the data from table1 to table2, data of all columns have trailing spaces.

What is the reason?  Is there a way to prevent this spaces?

Thanks. 

Reply

keithrull replied on 08-17-2006 9:58 PM

rated by 0 users

SpoonHP:

Hi,

I have problem moving data from one table to another table with different data type:

table1:

areacode varchar(50)

phone varchar(50)

table2:

areacode char(10)

phone char(15)

After moving the data from table1 to table2, data of all columns have trailing spaces.

What is the reason?  Is there a way to prevent this spaces?

Thanks. 

 

 

char type is fixed lenght padded to the right by spaces... while varchar is fixed lenght padded to the right by null values...

You can use the ANSI PADDING option to solve this issue. Here's some scenarios to see how this option works

char , NOT NULL , ANSI PADDING (ON) : Trailing spaces to length of column created

char, NOT NULL, ANSI PADDING (OFF): Trailing spaces to length of column created

char, NULL, ANSI PADDING (ON): Trailing spaces to length of column created

char, NULL, ANSI PADDING (OFF): Variable storage, no trailing spaces created, trailing spaces present are preseverd

varchar , NOT NULL , ANSI PADDING (ON) : Variable storage, no trailing spaces created, yet trailing spaces present are preserved

varchar, NOT NULL, ANSI PADDING (OFF): Variable storage, no trailing spaces created

varchar, NULL, ANSI PADDING (ON): Variable storage, no trailing spaces created,yet trailing spaces present are preserved

varchar, NULL, ANSI PADDING (OFF): Variable storage, no trailing spaces created