Databases
StackOverflow2013
Postsdbo
POUsing LIKE to match floats when Rounding is not wanted
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUsing LIKE to match floats when Rounding is not wanted
    primarykey
    data
    text
    <p>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.</p> <p>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.</p> <p>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.</p> <p>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. </p> <p>Then 12.34567 against, 12.34000. Then 12.3456 against, 12.3400 Then 12.345 against,12.340</p> <p>Then 12.34, against,12.34. </p> <p>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.</p> <p>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.</p> <p>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?"</p> <p>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.</p> <p>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.</p> <pre><code>SELECT COUNT(*) FROM dbo.StartCoord WHERE StartLatitude LIKE '12.817%' AND StartLongitude LIKE '12.819%' </code></pre> <p>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.</p> <p>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...</p> <pre><code>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 </code></pre> <p>...it Works!</p> <p>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.</p> <p>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?</p> <p>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.</p> <p>Thanks.</p> <p>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.</p> <p>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.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMPelletier
    1. USJoeManiaci
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POUsing LIKE to match floats when Rounding is not wanted
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POUsing LIKE to match floats when Rounding is not wanted
      1. USNenad Zivkovic
      1. VTFavorite
    3. VO
      singulars
      1. POUsing LIKE to match floats when Rounding is not wanted
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO+1 Good level of detail in the question, terrible approach...
      singulars
      1. POUsing LIKE to match floats when Rounding is not wanted
      1. USJosef K
    2. COI am sure you're referring to the LIKE. Everything else I found in regards to this problem involved rounding, which reduced my success of creating an association significantly.
      singulars
      1. POUsing LIKE to match floats when Rounding is not wanted
      1. USJoeManiaci
    3. COAfter reading your detailed explanation, I’m still not sure what exactly you want to base your comparison on actually? Since I assume that you want to match records with a “similar” value, wouldn’t it be better to compute the difference between the two float values, and then filter based on that difference being smaller then X?
      singulars
      1. POUsing LIKE to match floats when Rounding is not wanted
      1. USCBroe
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload