当不需要舍入时使用LIKE匹配浮点数 [英] Using LIKE to match floats when Rounding is not wanted

问题描述

这就是我要完成的任务。我在两个表中都有坐标数据，我正在尝试将它们与关联表链接在一起。这是一对一的关联，因此如果我在表A中有40条记录，在表B中有40条记录，那么我在关联表中应该有40条记录。

现在，这两个表中的数据大致相同，但从不无关紧要，实际上，它们甚至很少具有相同的精度。一个表（我们说A）总是有6个小数位，而表B可能没有小数位或最多6个小数位。

所以我们说重新匹配一对数据，例如表A中的12.345678与表B中的12.34。

所以我在asp.net代码中有一个foreach强制零到表B数据的末尾，因此我们首先将12.345678与12.340000进行比较。

然后将12.34567改为12.34000。
然后12.3456对阵12.3400
然后12.345对阵12.340

然后12.34对阵12.34。

只要关联记录尚不存在，并且在表A中包含对12.345678的引用，在表B中包含对12.34的引用，则会创建新的关联记录。 / p>

现在，乔，您可能会问，那么您如何将表A中的数据与表B中的数据进行比较？我把这部分留到最后，因为它是最奇怪的。

我正在使用LIKE，我相信这会使某些人不高兴，因为您已经在想，为什么在哎呀，您在使用LIKE，它是用于浮点数的字符串匹配吗？

好吧，因为到目前为止，效果最好，大约有95％的时间。其他5％的大多数只是因为数据差异太大，但是有一个非常奇怪的子集，最绝对应该匹配。

因此，在我插入一个记录，我检查匹配项，只要我只有一个匹配项，就创建关联记录。

  SELECT COUNT（ *）来自dbo.StartCoord 
 
，其中StartLatitude类似于'12 .817％'
和StartLongitude类似于'12 .819％'
  

现在，我现在正在查看12.817和12.819的记录，完整值实际上是12.8179和12.8199。因此，它应该可以工作，并且在95％的时间内都可以工作。

现在，对于奇怪的部分，也许可以使用LIKE（应该仅用于字符串匹配）导致SQL Server在后台进行舍入。我上面的stmt不起作用，但是如果我将其扔到Microsoft SQL Server Management中，然后将其更改为...

  SELECT来自dbo.StartCoord 
 
的COUNT（*），其中StartLatitude LIKE '12 .817％'-尝试字符串匹配12.8179 
和StartLongitude LIKE '12 .82％'-试图字符串匹配12.8199 
  

...有效！

我是假设有人会说这实际上不是LIKE，但是我将LIKE '12 .817％'与浮点数进行比较，并且该浮点数导致SQL Server制定一些舍入机制。

但是，如果是这种情况，为什么LIKE '12 .817％'会匹配原始的12.8179？

阅读完此文章后，如果有人有更好的标题，我可以用在其他人身上。

谢谢。

编辑：所以我完全忘记了提及为什么采用这种方法。在一个表中，实际的真实数据最多存储六个小数位，我认为我一直将其用作表A的示例。但是，表B中的数据（从没有小数位到第六位）有时会四舍五入，有时不会四舍五入。

因此在表A中，我们可能有12.123456，在某些情况下，它们为我们提供的表B数据可能是12.1234，有时也可能是12.1235。他们如何为我们提供数据并不一致，这就是为什么我采用这种方式进行研究。使用四舍五入或强制转换（数字）来处理这两种情况会减少创建关联的次数，但是我只是开始进行实验。我还找到了一个感兴趣的STR（）函数。

解决方案

如果您不想更改使用LIKE 。您可以将浮点数转换为十进制，然后转换为nvarchar，这将停止舍入问题。

  SELECT COUNT（*） FROM dbo.StartCoord 
 WHERE CAST（CAST（StartLatitude as DECIMAL（12,6））as nvarchar（20））比如'12 .817％'
 AND CAST（CAST（StartLongitude as DECIMAL（12,6）） ）as nvarchar（20））就像'12 .819％'
  

我假设所有小数点后6位需要。

So here is what I am trying to accomplish. I have coordinate data in two tables, which I am trying to link together with an Association table. It is a one to one association, so if I have 40 records in table A, 40 records in table B, then I should have 40 records in the association table.

Now the data in these two tables is approximately the same, but never idential, in fact they rarely even have the same precision. One table(we'll say A) always has 6 decimal places, whereas table B may have no decimal places or up to 6 decimal places.

So let's say we're just matching up one pair of data, say, 12.345678 in table A against table B, with 12.34.

So I have a foreach in my asp.net code that forces zeros onto the end of the table B data, so we first compare 12.345678 against 12.340000.

Then 12.34567 against, 12.34000. Then 12.3456 against, 12.3400 Then 12.345 against,12.340

Then 12.34, against,12.34.

So long as an association record doesn't already exist, containing a reference to 12.345678 in table A, or 12.34 in table B, a new association record is created.

Now you may be asking, Joe, so how are you comparing data in Table A against data in Table B? I saved this part for last because it's the weirdest.

I am using LIKE, which I am sure will upset some people because you're already thinking, "Why in the hell are you using LIKE, which is meant for string matches for floats?"

Well because it works the best so far, about 95% of the time. The majority of that other 5% is just because the data is too different, but there is a very odd subset that most absolutely should be matching.

So before I insert a record, I check for the match and so long as I have only one match, I create the association record.

SELECT COUNT(*) FROM dbo.StartCoord 

WHERE StartLatitude LIKE '12.817%' 
AND StartLongitude LIKE '12.819%'

Now I am looking right now at the record where the 12.817 and 12.819 came from, and the full values are actually 12.8179 and 12.8199. So it shoould work, and for 95% of the time it does work.

Now for the weird part, maybe, using LIKE(which should be only for string matching) is causing SQL Server to do rounding in the background. My above stmt does not work, but if I throw it in Microsoft SQL Server Management, and change it to...

SELECT COUNT(*) FROM dbo.StartCoord 

WHERE StartLatitude LIKE '12.817%' --trying to string match 12.8179
AND StartLongitude LIKE '12.82%'   --trying to string match 12.8199

...it Works!

I am assuming someone is going to say that it's not actually the LIKE, but the fact that I am comparing the LIKE '12.817%' to a float and that float is causing SQL Server to enact some rounding mechanism.

However, if that was the case, why would LIKE '12.817%' match the original 12.8179? Should it not have rounded as well, and only matched in the case of 12.82?

After reading this, if anyone has a better title I could use for anyone else in the future having the same problem, that would be great.

Thanks.

EDIT: So I completely forgot to mention why this approach was taken. In one table, actual truth data is stored up to six decimal places, which I think I consistently used as the Table A example. However, the data in Table B, that varies from no decimal places to six is sometimes rounded, sometimes not.

So in Table A we may have 12.123456 and in somes cases, they give us Table B data that could be 12.1234 or sometimes it could be 12.1235. How they give us data is not consistent, which is why I am working around it this way. Using rounding or casting(numeric) to handle both of these cases results in less Associations being created, but I've only started experimenting with that. I also found a STR() function I am interested to look at.

解决方案

If you do not want to change from using LIKE. You could cast the float to a decimal and then to an nvarchar, this should stop the rounding issue.

SELECT COUNT(*) FROM dbo.StartCoord 
WHERE CAST(CAST(StartLatitude  as DECIMAL(12,6)) as nvarchar(20)) LIKE '12.817%' 
AND   CAST(CAST(StartLongitude as DECIMAL(12,6)) as nvarchar(20)) LIKE '12.819%'

I have assumed 6 decimal places is all you need.

这篇关于当不需要舍入时使用LIKE匹配浮点数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！