SQL Server 将 varchar 转换为 int
我有一个表,其中有一列“值”,该列是 varchar。一行在此列中放置“10”。这个“数字”需要相加和相减,但我可以直接这样做,因为它是一个 varchar。 因此,以下给出了错误:
update Fields
set Value = Value - 1
from Fields f, FTypes ft
where ft.Name = 'Field Count'
and ft.ID = f.ID_FT
and f.ID_Project = 186
GO
如何将值转换/转换为 int,执行数学,然后再次设置为 varchar?
I have a table that has a column 'Value' that is a varchar. One row puts a '10' in this column. This "number" will need to be added and substracted to, but I can do so directly b/c its a varchar.
So, the following gives an error:
update Fields
set Value = Value - 1
from Fields f, FTypes ft
where ft.Name = 'Field Count'
and ft.ID = f.ID_FT
and f.ID_Project = 186
GO
How do I cast/convert the value to an int, perform the math, then set as a varchar again?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
马丁·史密斯的观点非常出色 -->如果只是输入数字数据,并且您总是要进行这样的操作,那么它将节省您的时间和麻烦,而不必进行这种转换工作。
话虽这么说,你可以这样做 -
其中 nn 是 varchar 列的原始定义
Martin Smith's point is an excellent one --> If it is only numeric data going in there and you are always going to be doing operations like this, it will save you time and hassle not having to do this conversion work.
That being said you can do -
where nn is the original definition of your varchar column
您需要使用
CAST
两次 - 一次使您的Value
列成为INT
,这样您就可以从中减去 1,然后返回到 < code>VARCHAR(x):此外,我建议在所有数据库对象上始终使用
dbo.
前缀,并且我始终主张使用新的 ANSI 标准 JOIN 语法,该语法更具表现力(更清晰易读)并理解)并有助于避免不需要的笛卡尔积(通过忘记在 WHERE 子句中指定 JOIN 条件......)You need to use
CAST
twice - once to make yourValue
column anINT
so you can subtract 1 from it, and then back to aVARCHAR(x)
:Also, I would recommend using the
dbo.
prefix always, on all your database objects, and I would always argue for the new, ANSI standard JOIN syntax which is more expressive (clearer to read and understand) and helps avoid unwanted cartesian products (by forgetting to specify a JOIN condition in the WHERE clause....)