SQL Server 将 varchar 转换为 int

发布于 2024-12-11 19:49:36 字数 339 浏览 0 评论 0原文

我有一个表,其中有一列“值”,该列是 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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

驱逐舰岛风号 2024-12-18 19:49:36

马丁·史密斯的观点非常出色 -->如果只是输入数字数据,并且您总是要进行这样的操作,那么它将节省您的时间和麻烦,而不必进行这种转换工作。

话虽这么说,你可以这样做 -

update Fields  
set ColumnName = cast( (cast(ColumnName as int) - 1) as varchar(nn))
from Fields f, FTypes ft
where ft.Name = 'Field Count'
    and ft.ID = f.ID_FT
    and f.ID_Project = 186   

其中 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 -

update Fields  
set ColumnName = cast( (cast(ColumnName as int) - 1) as varchar(nn))
from Fields f, FTypes ft
where ft.Name = 'Field Count'
    and ft.ID = f.ID_FT
    and f.ID_Project = 186   

where nn is the original definition of your varchar column

浅暮の光 2024-12-18 19:49:36

您需要使用 CAST 两次 - 一次使您的 Value 列成为 INT,这样您就可以从中减去 1,然后返回到 < code>VARCHAR(x):

update dbo.Fields  
set Value = CAST((CAST(Value AS INT) - 1) AS VARCHAR(20))
from dbo.Fields f
inner join dbo.FTypes ft ON ft.ID = f.ID_FT
where ft.Name = 'Field Count'
    and f.ID_Project = 186      

此外,我建议在所有数据库对象上始终使用 dbo. 前缀,并且我始终主张使用新的 ANSI 标准 JOIN 语法,该语法更具表现力(更清晰易读)并理解)并有助于避免不需要的笛卡尔积(通过忘记在 WHERE 子句中指定 JOIN 条件......)

You need to use CAST twice - once to make your Value column an INT so you can subtract 1 from it, and then back to a VARCHAR(x):

update dbo.Fields  
set Value = CAST((CAST(Value AS INT) - 1) AS VARCHAR(20))
from dbo.Fields f
inner join dbo.FTypes ft ON ft.ID = f.ID_FT
where ft.Name = 'Field Count'
    and f.ID_Project = 186      

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....)

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文