You need to sign in to do that
Don't have an account?
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.
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'
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.
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'....
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
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.
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.
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
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"?)
Thanks Simon,
I'll try that out!