+ Start a Discussion

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:


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





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.




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 : 



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...


There is not much and nothing is clearly described.


Anyway, thank you Sdry