I am working with a system that makes requests to SharePoint lists that have latitude and longitude columns. One of the most common use cases is requesting list items that fall within a certain bounding box.
Queries like this were working ...
<Geq> <FieldRef Name="Latitude"/> <Value Type="Number">-76.974777</Value> </Geq>
... until we discovered that some users had lists where the latitude and longitude columns were strings instead of numbers. On these lists, the query fails to return correct results because of issues with lexicographical ordering.
Before we ask users to change their data, we want to see if there is a code-only solution. At first, I checked the CAML query schema docs looking for some kind of type conversion operator, but couldn't find anything. Then, I found this SE StackExchange answer that suggests an algorithm which I think could be implemented in CAML (it needs adjustments for negative numbers):
[M]ake a custom comparator that first compares the length of the strings (shorter strings being smaller integers) and when they are equal compare the lexicographically (careful about leading '0')
Is this my only option? Is there some other way that I can create a comparison CAML query that would work on both number and string columns?