14

I'm trying to write a dynamic SOQL query to get an object's record that has numeric value in a particular field. I'm trying to do something similar like below:

Account acc;
queryString = 'SELECT Id,Name FROM Account WHERE Name like \'%[^0-9]%\' ORDER BY Name DESC limit 1';
acc = Database.query(queryString);

I do have an account with name = 123456 in my developer edition. But I'm getting error

"List has no rows for assignment to SObject"

In SQLServer we can use isnumeric() in the where clause to get the records with numeric values. Is there any similar function or alternative approach in SOQL to acheive this? Can anyone please help me?

Thanks in advance.

0

5 Answers 5

12

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.

4
  • 2
    If you have a large Account table, filtering on a formula field will be considered "nonselective". Oct 8, 2014 at 12:25
  • Custom indexes can be applied to deterministic formulas (which this counts as). Selectivity is too data dependent here to say much for sure though. Oct 8, 2014 at 14:04
  • If you want to index a formula, would that require a case with Support? Oct 8, 2014 at 14:33
  • It would - all custom index requests require a case at the moment. Oct 8, 2014 at 14:33
7

SOQL isn't SQL. While SQL does a bunch of nice pattern matching, SOQL pretty much only supports % in a LIKE clause. Things like brackets and dashes are taken literally. In fact, it's safe to say that there are no character class search capabilities in the query platform. You'd have to wildcard search 0 through 9 as ten different queries.

2
  • 4
    Just pointing out you could do this as one query (rather than 10 different ones) by using OR clauses: Name LIKE '0%' OR Name LIKE '1%' OR... etc.
    – jkraybill
    Oct 8, 2014 at 1:46
  • Or by using a LIKE and a Set, I've created an answer to illustrate Oct 8, 2014 at 10:50
4

And to add to the other answers, you could also use the following syntax:

Set<String> nums = new Set<String>{'0%','1%','2%','3%','4%','5%','6%','7%','8%','9%'};

Account acc;
String queryString = 'SELECT Id,Name FROM Account WHERE Name like :nums ORDER BY Name DESC limit 1';
acc = Database.query(queryString);
3
  • I like this, but it seems like it will only find Accounts where Name starts with a digit, not where the Name is fully numeric. You would still potentially have Accounts called "1st Class Service" or something. Oct 8, 2014 at 12:24
  • True...my regexp is not the best so I wasn't 100% sure what '%[^0-9]%' meant but I had assumed it meant has a number 0 to 9 in the name somewhere, not applicable if it means all chars MUST be numeric. Oct 8, 2014 at 12:29
  • Yeah, I think this is the closest you can get through pure SOQL. Oct 8, 2014 at 12:36
1

This might help anyone who can't use a formula field and still need to get the numeric values in the query result. I tried the below query and it worked.

String[] s = new String[]{'%a%','%b%','%c%','%d%','%e%','%f%','%g%','%h%','%i%','%j%','%k%','%l%','%m%','%n%','%o%','%p%','%q%','%r%','%s%','%t%','%u%','%v%','%w%','%x%','%y%','%z%',
    '%!%','%@%','%#%','%$%','%^%','%&%','%*%','%(%','%)%','%-%','%+%','%=%','%|%','%{%','%[%','%}%','%]%','%:%','%;%','%<%','%,%','%>%','%.%','%?%','%/%','%~%','%`%'};<br/>
List<Account> acc = new List<Account>();<br/>
String queryString = 'SELECT Id,Name FROM Account WHERE (not Name like :s) AND (not name like \'%\\\%%\') AND (not name like \'%\\\\_%\') AND (not name like \'%\\\\\\\%\') AND (not name like \'%\\\\\"%\') AND (not name like \'%\\\\\'%\')';<br/>
accList = Database.query(queryString);
0
0

I believe, nobody noticed it, here, but you issue here is not a Regex, but the fact that it does not return any records, from the query. In SF, if you set the return type to be a list and your query returns no results, you will get an empty list, but if you have a Single object, than you will get an exception.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .