SQL: ALTER COLUMN to shorter CHAR(n) type

sql alter-table alter-column
Vivian River picture Vivian River · Jun 16, 2010 · Viewed 21.4k times · Source

I'm working with MS SQL SERVER 2003. I want to change a column in one of my tables to have fewer characters in the entries. This is identical to this question: Altering a Table Column to Accept More Characters except for the fact that I want fewer characters instead of more.

I have a column in one of my tables that holds nine-digit entries. A developer previously working on the table mistakenly set the column to hold ten-digit entries. I need to change the type from CHAR(10) to CHAR(9).

Following the instructions from the discussion linked above, I wrote the statement

ALTER TABLE [MY_TABLE] ALTER COLUMN [MY_COLUMN] CHAR(9);

This returns the error message "String or binary data would be truncated". I see that my nine-digit strings have a space appended to make them ten digits.

How do I tell SQL Server to discard the extra space and convert my column to a CHAR(9) type?

Answer

František Žiačik picture František Žiačik · Jun 16, 2010

I think you get the error because there are some values in that table that are exactly 10 chars long (with no trailing spaces). Altering the table would thus cut these values to the length 9.

This is not allowed by default. If there only would be strings which would have some trailing spaces, there would be no problem with that.

So, if you are ok with cutting those values, do

UPDATE MY_TABLE SET MY_COLUMN = LEFT(MY_COLUMN, 9)

first, after that do the alter.

Related questions

How to alter a column's data type in a PostgreSQL table?

Entering the following command into a PostgreSQL interactive terminal results in an error: ALTER TABLE tbl_name ALTER COLUMN col_name varchar (11); What is the correct command to alter the data type of a column?

Read More
Change a Nullable column to NOT NULL with Default Value

I came across an old table today with a datetime column called 'Created' which allows nulls. Now, I'd want to change this so that it is NOT NULL, and also include a constraint to add in a default value (getdate()). …

Read More
SQL SET DEFAULT not working in MS Access

Possible Duplicate: DEFAULT clause in ALTER TABLE statement resulting in syntax error I am trying to execute the following statement using a SQL query within MS Access; ALTER TABLE [table] ALTER COLUMN [column] SET DEFAULT 'default value' However, I get …

Read More