更改列类型而不会丢失数据 [英] Change column type without losing data

问题描述

我正在使用SQL数据库，我有一列名为价格。创建数据库时，价格列设置为 NVARCHAR ，我需要将其类型更改为 decimal（18，2）而不会丢失数据库中的数据。这应该通过SQL脚本完成。

I am working on an SQL Database, I have a column named "Price". When the database was created the column "Price" was set to NVARCHAR I need to change its type to decimal(18, 2) without losing the data in the database. This should be done by an SQL Script

我想创建一个新列，将数据移到其中，删除旧列，然后重命名新创建的列。

I thought of creating a new column, moving the data to it, remove the old column, and then rename the newly created column.

有人可以帮我举一个例子吗？
SQL中是否还有一个函数可以将字符串解析为十进制？

Can someone help me with an example on how to do this? Also is there a function in SQL to Parse string to decimal?

谢谢

推荐答案

您不需要两次添加新列，只需在更新新列后删除旧列即可。

You don't need to add a new column two times, just remove the old one after updating the new one:

ALTER TABLE table_name ADD new_column_name decimal(18,2)

update table_name
set new_column_name = convert(decimal(18,2), old_column_name)

ALTER TABLE table_name DROP COLUMN old_column_name

请注意，如果 old_column_name 不是数字，转换可能会失败。

Note that if the old_column_name is not numeric, the convert may fail.

这篇关于更改列类型而不会丢失数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！