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.

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.

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