SQL: 将列更改为较短的CHAR(n)类型

Question

我正在使用MS SQL SERVER 2003。我想要更改一个表格中的列，使其条目字符数减少。这与此问题完全相同：更改表列以接受更多字符，不同的是我需要减少字符数而不是增加。

我有一个包含9位数字的列。之前在该表上工作的开发人员错误地将列设置为容纳10位数字。我需要将类型从CHAR(10)更改为CHAR(9)。

根据上面链接的讨论中的说明，我编写了以下语句：

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

这会返回错误消息“将截断字符串或二进制数据”。我看到我的9位数字字符串后面附加了一个空格，以使它们变成10位数。

我该如何告诉SQL Server丢弃额外的空格并将我的列转换为CHAR(9)类型？

- Vivian River

Answer 1

我认为你之所以会出现这个错误，是因为该表中有一些值恰好为10个字符（没有尾随空格）。修改该表会将这些值截断为长度为9。

默认情况下不允许这样做。如果只有字符串有一些尾随空格，那么就不会有问题。

因此，如果你可以接受截断这些值，请先执行以下操作：

UPDATE MY_TABLE SET MY_COLUMN = LEFT(MY_COLUMN, 9)

然后再进行修改。

- František Žiačik

---

接受：这个方法可行。有趣的是，如果没有空格附加，这些值都不会超过9个字符。然而，在ALTER语句之前使用您推荐的UPDATE语句可以使其工作。 - Vivian River

请注意，对于稍微通用一些的问题，在某些情况下，这种方法不起作用。LEFT 返回带有最多 n 个字符的字符串，但在某些编码中每个字符可以有多个字节（例如 utf8mb4_general_ci）。MariaDB 中的数据类型，如 text，具有以字节为单位的最大大小，而不是字符数。（很抱歉让您看到这篇“僵尸帖子”，但它是某些查询的谷歌搜索结果的第一名） - BorysekOndrej

Answer 2

在修改表之前，要先禁用Ansi Warnings。

SET ANSI_WARNINGS OFF

请注意，如果您的数据长度恰好为10个字符，则数据将被截断。

编辑

在实际更改列长度之前，请检查现有长度。

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

- Lieven Keersmaekers

---

我会先编写代码来检查第10个字节是否被设置，如果是则报错。 - Philip Kelley

Answer 3

上面的两个答案对我没有用，因为我在尝试修改的表上有一个关联的历史表。这是我做的：

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

- Joels Elf