CODEWITHSUNDEEP
sqlalter-tablealter-column
Vivian River
Vivian River

Reputation: 32410

SQL: ALTER COLUMN to shorter CHAR(n) type

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?

Upvotes: 16

Views: 25702

Answers (3)

Joels Elf
Joels Elf

Reputation: 784

The above 2 answers didn't work for me because I had a history table attached to the table I was trying to alter. This is what I did:

UPDATE MY_TABLE SET MY_COLUMN = LEFT(MY_COLUMN, 9)
ALTER TABLE MY_TABLE SET (SYSTEM_VERSIONING = OFF)
-- Might need to UPDATE MY_TABLEHistory SET MY_COLUMN = LEFT(MY_COLUMN, 9)
-- I didn't
ALTER TABLE MY_TABLEHistory ALTER COLUMN [MY_COLUMN] [varchar] (9) NULL
ALTER TABLE MY_TABLE SET (SYSTEM_VERSIONING = ON(HISTORY_TABLE = MY_TABLEHistory))
ALTER TABLE MY_TABLE ALTER COLUMN [MY_COLUMN] [varchar] (9) NULL

Upvotes: 0

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58491

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

Upvotes: 12

František Žiačik
František Žiačik

Reputation: 7614

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.

Upvotes: 22

Related Questions