問題描述

我有一個帶有 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的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！