+ Start a Discussion
adamgadamg 

Documentation Errata: Use of LIKE in SOQL

Please note there is an omission in the API 2.5 documentation concerning SOQL and LIKE.

 

The documentation is missing description of the LIKE keyword, which as in the case with SQL, allows wild card searching of text fields.

 

For example:

 

select id, firstname, lastname  from lead where lastname like 'Smith%'

 

Will match all last names starting with Smith.

 

select id, firstname, lastname  from lead where lastname like 'Smith_'

 

Will match all last names starting with Smith up to 1 additional character.  (IE Smiths will match, but Smithson will not.)

 

The documentation will be updated soon to reflect this feature.

jtatejtate
I don't know if this is appropriate to post here, but I think it is. It tripped me up, and as the examples in the documentation are insufficient, it should be noted somewhere.

The fieldEspression documentation mention that for the fieldName,
"The name of a field in the specified object enclosed in single quotes (the use of double quotes will result in an error). You must have at least read-level permissions to the field. It can be any field-it does not need to be a field in the fieldList." [emphasis added, italics in original].

This is incorrect. There must not be quotes--single, double or otherwise--around the field name. So, a query using a where clause should look like the following:

select Id, Name from Account where Name='foo'
adamgadamg
Hmm - looks like you are right.  I'll file a bug - thanks.
djordandjordan

Is it possible to use the LIKE operator on picklist fields?

Our experimentation suggests that when we query on a picklist using LIKE we get an empty response. If we convert the field to type text then the query behaves as expected.

Is the use of LIKE restricted to text fields? If so, perhaps it should return something like fault 1223 "Invalid operator in query filter criterion" instead of an empty resultset.

 

D.

 

Michael SMichael S

How can I form a "like" query that is case-insesitive:

I want to find all word documents without making a ton of like statements:

like '%.doc' or like '%.Doc' or like '%.DOC' or like '%.dOc'....

DevAngelDevAngel

Hi Michael S,

The like operator is case insensitive.  So like '%.doc' should return .DOC and .DOc and .DoC and .Doc and so on.

Cheers

DevAngelDevAngel

Hi djordan,

This is going to be in either a patch release or in the next Major release in April.  You definitely should be able to use like with any text-ish field including picklists.

Cheers.

Lindz-Lindz-

Phone is currently story in this format in our database (631) xxx-xxxx

I would like to be able to query based on the number being formatted like so 631xxxxxxx

Is this possible?, right now I'm forced into doing this...

Select Id, Phone, LastName from Contact where Phone like '%631%xxx%xxxx'

or this :

Select Id, Phone, LastName from Contact where Phone like '(631) 940-4216'

Which is pretty horrible for me as the original input is passed via a URL.

Lindz-Lindz-
I worked around this but I'm still curious...
aadevaadev
It looks like the fix for this didn't make it into 4.0. When can we expect this to be fixed?
aadevaadev
Actually, I meant the 4/2004 release of the 3.0 API. Sorry.
Gagan.ax66Gagan.ax66

Hi ,

Please let me know if i can use like query for Picklist values. I want to replicate a filter criterion just like in mass lead transfer. i have seen picklist can be searched there with "starts with" and "contains" operators.

But API does not support this when i use " ...... where field_name  like '%xx%'"

is there any other way to do that...


Thanks,
Gagan

DevAngelDevAngel
No, that kind of criteria is not available on picklists.
CodeTalkerCodeTalker

Can you tell me what SOQL statement is used (under the hood) when a user selects the "contains" operator for a picklist (when creating a new View)? (Or the other operators "starts with", "does not contain"?)

 

 

 

 

 

 

 

 

SuperfellSuperfell
none, views don't use soql. But the soql equivlent would be includes.
CodeTalkerCodeTalker

Thanks Simon,

I'll try that out!