function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
DJHDJH 

Querying for objects NOT in set

I'm using the 4.0 API under Java, not that it matters for this....

I want to select all leads in the US which do not have a two-character state code in the state column. In other words, "select company, city, state from lead where country='USA' and length(state) != 2". However, the Salesforce SOQL doesn't seem to have a "length" function.

So alternatively I would like to do something like this: "select company, city, state from lead where country='USA' and state not in ('AL' 'AK' ... 'WY')". However, the Salesforce SOQL doesn't seem to support this syntax, either.

So how can I accomplish this?

DJH
adamgadamg
You can chain together "like" (or NOT like) statements..

select x from y where (z like a) or (z like b) and (z not like c)

etc.. Not ideal but it works. SOQL does not, however, support string operations.
DJHDJH
It seems to me that this would yield the "good" matches, as well as the "bad" ones that I'm really trying to capture, though. For example if I do "select state from lead where (state like 'AK') or (state like 'CA')", I will get all of these:

AK
CA
Alka
Calif

But I don't want the AK and CA, I want only the Alka and the Calif, so that I can fix them. And so on with all states. Is there a way do this?

DJH
DevAngelDevAngel

Hi DJH,

Have you tried using the underscore wildcard char?

I think what you want is something like this:

where state like '___%' which should yield and state that has at least 3 chars.
DJHDJH
Thank you, Dave! That is exactly what I needed. I was unaware of this wildcard option.

DJH