从表中选择最近值 - 将数据类型nvarchar转换为数字时出错。 [英] Choose Nearest Value from table - Error converting data type nvarchar to numeric.

问题描述

大家好，



我想从表中选择最近的下限和上限值，我在下面查询使用

Hi All,

I want to choose nearest lower and upper values from table for that I am using below query

DECLARE @lat1 decimal(18,6)
DECLARE @lon1 decimal(18,6)

SET @lat1 = 17.43969
SET @lon1 = 78.4842

SELECT TOP 5 ID, Cl_Name,lat1,lon1 
from MyTable
ORDER BY ABS(cast(lat1 as decimal(18,6)) - @lat1)

如果我运行上面的查询，它会给我以下错误 -

转换数据类型时出错 nvarchar 到数字。





这个查询有什么问题

If I run above query, it gives me below error-
Error converting data type nvarchar to numeric.


What is wrong in this query

推荐答案

为什么要将数据库值转换为字符串，以便尝试获取它的绝对值？如果它已经是数字，那么这是毫无意义的浪费时间 - 因为它必须再次转换回数字才能让ABS工作。

如果它是一个字符串，那么它没有任何用处或者。



我猜你的lat1列包含字符串数据，并且它包含SQL不期望作为数字的一部分的字符。如果是这样，那么您需要更改数据库并将数值存储为数字...或者此问题将定期重现。

Why are you casting your database value to a string, in order to try and take the absolute value of it? If it's numeric already, then it's a pointless waste of time - since it has to be converted back to a number again in order for ABS to work.
And if it's a string, then it does nothing useful either.

I'm guessing that you lat1 column contains string data, and that it contains characters that SQL isn't expecting as part of a number. If so, then you need to change your DB and store numeric values as numbers...or this problem will recur on a regular basis.

如果此查询运行良好并且现在开始获取失败然后看起来你在Lat1列中有坏数据。您可以使用以下查询识别这些错误的行。一旦数据得到纠正，您自己的查询就可以正常运行。

If this query was working well and now started getting fail then it seems that you have bad data in Lat1 column. You can identify these bad rows using following query. Once data gets corrected your own query will run fine.

SELECT ID, Cl_Name,lat1,lon1
FROM MyTable
WHERE ISNUMERIC(lat1) <> 1;

您好Prajakta，

请执行以下代码。

Hi Prajakta,
Do like below code.

DECLARE @lat1 decimal(18,6)
DECLARE @lon1 decimal(18,6)

SET @lat1 = 17.43969
SET @lon1 = 78.4842

SELECT TOP 5 ID, Cl_Name,@lat1,@lon1
from MyTable
ORDER BY ABS(cast(@lat1  as decimal(18,6)) - @lat1)

由： Nirav Prabtani

设置语言代码块

[/编辑]

By :Nirav Prabtani
Set language code block
[/Edit]

这篇关于从表中选择最近值 - 将数据类型nvarchar转换为数字时出错。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！