T-SQL:将 NTEXT 转换为 VARCHAR 到 INT/BIGINT [英] T-SQL: Converting NTEXT to VARCHAR to INT/BIGINT

问题描述

我有一个带有 NTEXT 类型字段的表，它存储多种类型的值，其中文件大小.我正在尝试对记录列表运行查询并将文件大小相加，但我遇到了这个令人困惑的问题.

I have a table with a field of type NTEXT which stores many type of values, filesize among them. I'm trying to run a query on a list of records and add up the file sizes but I'm encountering this perplexing problem.

由于NTEXT无法直接/隐式转换为INT或BIGINT，我先将其转换为VARCHAR 然后我试图将其转换为 INT 或 BIGINT.一切正常，直到我尝试将 VARCHAR 值转换为 INT 或 BIGINT.

Since NTEXT cannot be directly/implicitly converted to INT or BIGINT, I'm converting it first to VARCHAR then I'm trying to convert it to either INT or BIGINT. All goes fine until I try to convert the VARCHAR value to INT or BIGINT.

这是我的查询和结果:

首先我尝试以下操作，结果显示没有问题，输出为 61069(值仍为 ntext 类型).

First I try the following, which shows no problems and the output is 61069 (value still as ntext type).

SELECT FileSize
FROM dbo.myTable
WHERE ID = 111

现在我将它转换/转换为 varchar，再次，没问题.输出为 61069(现在是 varchar 类型).

Now I convert/cast it as varchar, and again, no problem. The output is 61069 (now varchar type).

SELECT CONVERT(VARCHAR, FileSize)
FROM dbo.myTable
WHERE ID = 111

最后，我尝试将 VARCHAR 值转换为 BIGINT，以便我可以进行 SUM() 和其他计算，但这次我收到将数据类型 varchar 转换为 bigint 时出错."留言.

Finally, I try to convert the VARCHAR value into BIGINT so that I can do my SUM() and other calculations, but this time I get a "Error converting data type varchar to bigint." message.

SELECT CONVERT(BIGINT, CONVERT(VARCHAR, FileSize))
FROM dbo.myTable
WHERE ID = 111

如果我尝试将其转换为 INT，则会出现将 varchar 值 '7/1/2008 3:39:30 AM' 转换为数据类型 int 时转换失败"

And if I try converting it to INT instead, I get a "Conversion failed when converting the varchar value '7/1/2008 3:39:30 AM' to data type int"

SELECT CONVERT(INT, CONVERT(VARCHAR, FileSize))
FROM dbo.myTable
WHERE ID = 111

我完全迷失了，有什么可能导致这种情况的想法吗?

I'm absolutely lost, any ideas of what could be causing this?

推荐答案

您无法控制 where 子句和转换的应用顺序.在某些情况下，SQL Server 会尝试对未通过过滤器的行执行转换 - 这一切都取决于计划.试试这个:

You can't control the order in which the where clause and conversions apply. In some cases SQL Server will attempt to perform the conversion on rows that wouldn't pass the filter - all depends on the plan. Try this instead:

SELECT CASE WHEN ID = 111 THEN 
  CONVERT(INT, CONVERT(VARCHAR(12), FileSize))
  -- don't be lazy, size ------^^ is important
  END
FROM dbo.myTable
WHERE ID = 111;

还要考虑使用整数列来存储整数.然后你就不会在 FileSize 列中出现像 '7/1/2008 3:39:30 AM' 这样愚蠢的废话.

Also consider using an integer column to store integers. Then you don't end up with goofy nonsense in the FileSize column like '7/1/2008 3:39:30 AM'.

这篇关于T-SQL:将 NTEXT 转换为 VARCHAR 到 INT/BIGINT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！