SQL: ATER COLUMN to shorter CHAR(n) type
- by Rising Star
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: http://stackoverflow.com/questions/2281336/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?
