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.


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.

  • 2
    If you have a large Account table, filtering on a formula field will be considered "nonselective". – Jeremy Nottingham Oct 8 '14 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. – ca_peterson Oct 8 '14 at 14:04
  • If you want to index a formula, would that require a case with Support? – Jeremy Nottingham Oct 8 '14 at 14:33
  • It would - all custom index requests require a case at the moment. – ca_peterson Oct 8 '14 at 14:33

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.

  • 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 '14 at 1:46
  • Or by using a LIKE and a Set, I've created an answer to illustrate – Phil Hawthorn Oct 8 '14 at 10:50

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);
  • 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. – Jeremy Nottingham Oct 8 '14 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. – Phil Hawthorn Oct 8 '14 at 12:29
  • Yeah, I think this is the closest you can get through pure SOQL. – Jeremy Nottingham Oct 8 '14 at 12:36

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%',
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);

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.