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.

Answer

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.

Attribution
Source : Link , Question Author : user12106 , Answer Author : ca_peterson

Leave a Comment