SQL Server - 将数据类型 nvarchar 转换为 bigint 时出错
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41264655/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server - Error converting data type nvarchar to bigint
提问by Luká? Irsák
When I run following query with SELECT *I get error saying :
当我运行以下查询时，SELECT *我收到错误消息：
[S0005][8114] Error converting data type nvarchar to bigint.
[S0005][8114] 将数据类型 nvarchar 转换为 bigint 时出错。
SELECT * FROM (
SELECT * , ROW_NUMBER() OVER (ORDER BY CAST(id as BIGINT)) AS RowNum
FROM users
) AS users
WHERE users.RowNum BETWEEN 0 AND 5 ;
When I run this query only with SELECT id , ROW_NUMBER() ...everything works.
当我运行此查询时，SELECT id , ROW_NUMBER() ...一切正常。
My DB looks like this:
我的数据库看起来像这样：
This query run well with other table where idcolumn is NVARCHAR
此查询与id列所在的其他表运行良好NVARCHAR
ID column is number only and if i cast it as : CAST(id as NVARCHAR) i get same error.
ID 列仅是数字，如果我将其转换为：CAST(id as NVARCHAR)，则会出现相同的错误。
EDIT:
编辑：
I Found problem with column ID values
我发现列 ID 值有问题
ID 46903836 ID 9100000004
身 46903836 身 9100000004
Small ids dont have leading zeros
小 ID 没有前导零
回答by Wes Palmer
Usually when I get this error it is because there is whitespace on the front or end of the column. Here is how I fix it.
通常，当我收到此错误时，是因为列的前端或末尾有空格。这是我修复它的方法。
SELECT * FROM (
SELECT * , ROW_NUMBER() OVER (ORDER BY CAST(LTRIM(RTRIM(id)) as BIGINT)) AS RowNum
FROM users
) AS users
WHERE users.RowNum BETWEEN 0 AND 5 ;
This will ensure ID is just the number only I am also assuming that there aren't any alpha characters with the ID.
这将确保 ID 只是数字，我还假设 ID 没有任何字母字符。
回答by Joe Taras
Your IDfield is BIGINT(you have posted your table structure), this don't cause the error in your question.
您的ID字段是BIGINT（您已经发布了您的表结构），这不会导致您的问题出现错误。
But, because is unuseful the CASTyou can rewrite your query as follow:
但是，因为没有用，CAST您可以按如下方式重写查询：
SELECT * FROM (
SELECT * , ROW_NUMBER() OVER (ORDER BY id) AS RowNum
FROM users
) AS users
WHERE users.RowNum BETWEEN 0 AND 5 ;
回答by Satyam Kundula
You Don't need to cast your id column as it is already in bigint datatype
您不需要转换您的 id 列，因为它已经在 bigint 数据类型中
