在 SQL Server 的 SELECT 中更改列数据类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28663276/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Change column datatype in SELECT in SQL Server
提问by roYal
I want to change my column value in categoriIDfrom numbers to text.
我想将我的列值categoriID从数字更改为文本。
Is this possible?
这可能吗？
SELECT name, CAST(categoriID AS char(10))
FROM customer
WHERE categoriID = 1 AS 'new_text'
Here is a link of a pic how i want it: http://i.imgur.com/EFWNH3w.png
这是我想要的图片链接：http: //i.imgur.com/EFWNH3w.png
采纳答案by Bogdan Sahlean
1) Simplest solution would be a simple join thus:
1）最简单的解决方案是一个简单的连接，因此：
SELECT c.name, c.categoryID, category.name AS category_name
FROM customer c
INNER JOIN -- or LEFT JOIN if categoryID allows NULLs
(
SELECT 1, 'First category' UNION ALL
SELECT 2, 'Second category' UNION ALL
SELECT 3, 'Third category'
) category(categoryID, name) ON c.categoryID = category.categoryID
I would use this solution if list of categories is small, static and if it is needed only for this query.
如果类别列表很小、是静态的并且仅用于此查询，我将使用此解决方案。
2) Otherwise, I would create a new table thus
2）否则，我会创建一个新表
CREATE TABLE category -- or dbo.cateogory (note: you should use object's/table's schema)
(
categoryID INT NOT NULL,
CONSTRAINT PK_category_categoryID PRIMARY KEY(categoryID),
name NVARCHAR(50) NOT NULL -- you should use the propper type (varchar maybe) and max length (100 maybe)
--, CONSTRAINT IUN_category_name UNIQUE(name) -- uncomment this line if you want to have unique categories (nu duplicate values in column [name])
);
GO
plus I would create a foreign key in order to be sure that categories from [customer] table exist also in [category] table:
另外，我会创建一个外键，以确保 [customer] 表中的类别也存在于 [category] 表中：
ALTER TABLE customer
ADD CONSTRAINT FK_customer_categoryID
FOREIGN KEY (categoryID) REFERENCES category(categoryID)
GO
INSERT category (categoryID, name)
SELECT 1, 'First category' UNION ALL
SELECT 2, 'Second category' UNION ALL
SELECT 3, 'Third category'
GO
and your query will be
你的查询将是
SELECT c.name, c.categoryID, ctg.name AS category_name
FROM customer c
INNER JOIN ctg ON c.categoryID = ctg.categoryID -- or LEFT JOIN if c.categoryID allows NULLs
I would use solution #2.
我会使用解决方案#2。
回答by andrewgu
From this possible duplicate SOon SQL Server 2008:
从SQL Server 2008 上这个可能的重复 SO开始：
EXEC sp_RENAME table_name , old_name, new_name
Or you could do this:
或者你可以做到这样：
ALTER TABLE table_name RENAME COLUMN old_name to new_name;
EDIT:Just got the question. To change the datatype of the column to a text datatype, you could execute this:
编辑：刚刚得到问题。要将列的数据类型更改为文本数据类型，您可以执行以下操作：
ALTER TABLE table_name MODIFY column_name CHARACTER(n);
where nis the number of characters in the string, or:
其中n是字符串中的字符数，或：
ALTER TABLE table_name MODIFY column_name VARCHAR(n)
where nis the maximum number of characters in the string.
其中n是字符串中的最大字符数。
Note that these will edit the original table. If you just want to select the column as a particular datatype, you'll need to copy the table using:
请注意，这些将编辑原始表。如果您只想选择该列作为特定数据类型，则需要使用以下方法复制表：
SELECT column_name(s) INTO newtable [IN externaldb] FROM table1;
Then, you can modify the column datatype as shown above, and DROPthe new table if you want to. Another way to do this without a separate table would be to use CASTor CONVERT
然后，您可以修改如上所示的列数据类型，DROP如果需要，还可以修改新表。在没有单独表的情况下执行此操作的另一种方法是使用CAST或CONVERT
回答by Arun D
You can use this Query to change Column datatype,
您可以使用此查询来更改列数据类型，
Syntax:
句法：
ALTER TABLE table_name ALTER COLUMN column_name datatype;
Solution:
解决方案：
ALTER TABLE customer ALTER COLUMN categoriID Varchar(50);
回答by Ismail Gunes
ALTER TABLE customer CHANGE categoriID categoriID TEXT
ALTER TABLE 客户 CHANGE categoriID categoriID TEXT
