SQL: ATER COLUMN to shorter CHAR(n) type

Question

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?

Answer 1

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.

Answer 2

Disable Ansi Warnings before you alter your table.

SET ANSI_WARNINGS OFF

Beware that data will be truncated if you happen to have something 10 characters long.

Edit

Check existing lengths before actually changing the column length.

SET ANSI_WARNINGS OFF
GO

CREATE TABLE Test (Value CHAR(10))
INSERT INTO Test SELECT ('1234567890')

IF NOT EXISTS (SELECT * FROM Test WHERE LEN(Value) > 9)
  ALTER TABLE Test ALTER COLUMN Value CHAR(9)
ELSE
  SELECT LEN(Value), * FROM Test WHERE LEN(Value) > 9

DROP TABLE Test