You need to sign in to do that
Don't have an account?
String comparison in SOQL query
Dear all,
I am wondering how String comparison are performed in SOQL queries. Let me explain myself:
My query is something like that:
[SELECT Id FROM User_Profile__c WHERE Contact__r.Name >= :contactNameMin AND Contact__r.Name <= :contactNameMax]
Here are some of the results I obtain with some boundaries (contactNameMin and contactNameMax):
Between 1 and 100: 1 record
Between 100 and 2: 100 records
Between 1 and 2: 101 records (as expected).
Everything works fine with numbers. Everithing goes weird when I mix numbers and letters.
I tried all orders as I don't know how comparison is performed.
Between 2 and A: 0 record
Between 1 and A: 0 record (I assumed then that letters are before numbers in order)
Between A and 100: Still 0 record !!
Can someone enlight me, I am completely lost !
Please help!
Thank you beforehand
Hi,
What exactly is your "contactNameMin" and "contactNameMax"?
Also if you could explain in brief what exactly you want to achieve.
In the examples I provided, contactNameMin and contactNameMax are the boundaries I provided.
For the first example, contactNameMin = '1' and contactNameMin = '100'.
My use case is: I have a lot of user profiles and i am unabled to display them all so I present user two filtering fields (to enter the boundaries). These fields are used to filter the list and display only a part of the whole set of user profiles.
Hi,
In that case you can plan to have a number field on your custom object and then use it in your SOQL that will solve your problem.
Hi again,
yes of course I could do that but I am not really looking for a bypassing solution. I just want to understand how comparison is performed for Strings in SOQL query, on what basis, is 1>A or the opposite. Because regarding my example I am realy lost.
Are the rules explained somewhere?
I have not seen this in any of the documents.
But there is one feature that will be coming up called as OFFSET queries (not sure but i think it is available in spring '12 release). You can try it.
Here's the link :
http://blogs.developerforce.com/developer-relations/2012/01/soql-offset-in-spring-12.html
Thank you for your answers kulkarni,
But I am really just interested in understanding comparison behavior regarding my examples.
If someone else has any information about that...
I think this is all the official documentation out there: http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_soql_select_comparisonoperators.htm
There is not much and nothing is clearly described.
Anyway, thank you Sdry