SOQL query to return records with numeric values

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.


Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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.

Method 2

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.

Method 3

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);

Method 4

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);

Method 5

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.

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Notify of

Inline Feedbacks
View all comments
Would love your thoughts, please comment.x