+ Start a Discussion
AWL-CRMAWL-CRM 

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

 

MandyKoolMandyKool

Hi,

 

What exactly is your "contactNameMin" and "contactNameMax"?

 

Also if you could explain in brief what exactly you want to achieve.

 

 

AWL-CRMAWL-CRM

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.

MandyKoolMandyKool

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.

 

 

AWL-CRMAWL-CRM

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?

MandyKoolMandyKool

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 

AWL-CRMAWL-CRM

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

AWL-CRMAWL-CRM

There is not much and nothing is clearly described.

 

Anyway, thank you Sdry