Menu
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?
997
it is not possible to decrease the size of column. same table it is not possible.
ALTER TABLE table_name MODIFY column_name varchar(new_length); In the above command, you need to specify table_name whose column you want to modify, column_name of column whose length you want to change, and new_length, new size number. Let us increase size of product_name from varchar(20) to varchar(255).
In generic terms, you use the ALTER TABLE command followed by the table name, then the MODIFY command followed by the column name and new type and size. Here is an example: ALTER TABLE tablename MODIFY columnname VARCHAR(20) ; The maximum width of the column is determined by the number in parentheses.
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.
120
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
