SOQL query where condition for non alpha characters

apexsoql

I have a field which contains values like [1234] – Sample, [1234F] – Face Sample and further more. I want to get the rows using SOQL query with this field in where condition, when using below query there are no rows returned

Select Name, Id, Sample__c from Custom_Object__c where Sample__c = '[1234] - Sample'

Even checked in Developer Console under Query tab, it did not returned any rows. But with below query I got results (which included few unwanted rows too because of Like condition)

Select Name, Id, Sample__c from Custom_Object__c where Sample__c LIKE '%1234%'

But I want only rows which were matching with entire value rather than LIKE. Please suggest me how to achieve it.

Note sometimes values can be "[1234] & [1234F] – Sample case"

Best Answer

That query should work. However, please be aware that there may be multiple contiguous spaces in your string; make sure you're paying attention. For example, here's an example using an Account:

As you can see, I had no problem querying the record. So, there might be an extra space somewhere, such as [1234] - Sample or [1234] - Sample. These extra spaces may not always appear in some cases, so try querying the record and checking it carefully using the Data Loader or Query Editor.

Related Solutions

[SalesForce] SOQL query to return records with numeric values

As sfdcfox points out there isn't support for this natively in SOQL, however formula fields can in many cases be leveraged to do this type of work.

Creating a new formula field on the account object of type checkbox with a formula of ISNUMBER(Name) should get you a new boolean column you can filter on for this effect. As formula field values are generated on the fly during the query, much like SQL functions, this has a very similar result - it just has to be prepared ahead of time in a formula field instead of being embedded in the query at runtime.

[SalesForce] Is SOQL Polymorphism also supported in WHERE clause

Based on the current TYPEOF documentation that clause is only applicable within the SELECT statement.

TYPEOF is an optional clause that can be used in a SELECT statement of a SOQL query when you’re querying data that contains polymorphic relationships.

You might need to use something like What.Type = 'Account' and What.Phone = '123456' in the SOQL query. Maybe build it up with some additional conditions.

E.g.

SELECT ID, WhatId 
FROM Event 
WHERE 
    (What.Type = 'Account' and What.Phone = '12345') or 
    (What.Type = 'Campaign' and What.Name = '12345')

Oddly, as @Anil observes in the comments, other Account fields produce an error:

SELECT ID, WhatId, What.Type
FROM Event 
WHERE 
    (What.Type = 'Account' and What.BillingCity = '12345')

gives:

INVALID_FIELD:
(What.Type = 'Account' and What.BillingCity = '12345')
ERROR at Row:4:Column:36
No such column 'BillingCity' on entity 'Name'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names.

I suspect the What.Type = 'Account' guard statement isn't preventing the column checking on the polymorphic What field.

I was going to suggest that if all the types referenced by the field have the field being checked then the filter will work and if the field only belongs to a subset of the types they you will get the error. However, this doesn't appear to be the case either, as Campaign doesn't have a Phone field.

Best Answer

Related Solutions

[SalesForce] SOQL query to return records with numeric values

[SalesForce] Is SOQL Polymorphism also supported in WHERE clause

Related Topic