Find all-numeric values in an alphanumeric field

Question

There is a name field which is set to alphanumeric in a table, the table is a very very large one. Is there a way to query the list where the name entered in this name field is all numeric?

The table name is list.
The filed name is holder_name.

I want to know if there are cases where the holder name is made up of entirely digits like 123456 and holds no letters.

I have tried using like but that didn't work.

To make it a little clearer, any solution to this is completely dependent on the RDBMS. — Andrew, Mar 20 '23 at 16:53

Erwin Brandstetter · Answer 1 · 2023-03-20T16:48:54.163

0

Use a regular expression match:

SELECT *
FROM   list
WHERE  holder_name ~ '^\d+$';  -- entirely digits

PostgreSQL documentation. (You didn't name your RDBMS.)

edited Mar 20 '23 at 16:48

answered Mar 20 '23 at 16:43

Erwin Brandstetter

605,456
145
1,078
1,228

score 0 · Answer 2 · answered Mar 20 '23 at 18:50

Your previous tags suggest you used to use Oracle (and Oracle Forms); if that's still true, here are two options.

Sample data:

SQL> select * from list;

HOLDER
------
abc123
123456       --> this is the only value which consists of digits only
abcdef
12cd45
ab34ef

If it is still about Oracle Forms, depending on version you use you might find out that regular expressions won't work:

SQL> select holder_name
  2  from list
  3  where regexp_like(holder_name, '^\d+$');

HOLDER
------
123456

In that case, try translate function which works everywhere (in Oracle):

SQL> select holder_name
  2  from list
  3  where translate(holder_name, 'x0123456789', 'x') is null;

HOLDER
------
123456

SQL>

Thankyou for the reply. It worked like a charm. Another question if i may, can you suggest any course etc that would help me getting skilled in MySQL, PLSQL and jn genrall? — Zehr_rheZ, Mar 23 '23 at 15:11
I know Oracle offers various courses (and they are rather expensive). For anything else, I should Google for it but - that's what you can do yourself. — Littlefoot, Mar 23 '23 at 18:57

score 0 · Answer 3 · answered Mar 20 '23 at 19:30

This works for MySQL:

select holder_name
from list
where CAST(CAST(holder_name AS UNSIGNED) AS CHAR) = holder_name

see: DBFIDDLE-MySQL

But needs some adaptations for MSSQL:

select holder_name
from list
where CAST(TRY_CAST(holder_name AS INTEGER) AS CHAR) = holder_name

see: DBFIDDLE-MSSQL

score 0 · Answer 4 · answered Mar 22 '23 at 14:05

You can use POSIX regexp_like() the exact same way in Postgres, Oracle and MySQL:

SELECT * FROM list WHERE regexp_like(holder_name,'^[0-9]+$');

the [0-9] meaning [0123456789] character class targets only digits
+ says there must be at least one character belonging to that digit class
surrounding ^ and $ say you don't want anything else in front or at the end

The SQL standard specifies pattern matching, but as with everything it says, different RDBMS implement and call it differently, or even don't - satisfied with trading full compliance for performance, backward-compatibility or other reasons.

(Microsoft) SQL Server requires an extension or UDF to do more than LIKE is capable of.
In SQLite there's a regexp function and operator declared but undefined until you do it.
MariaDB offers a predefined regexp.

I want to know if there are cases where the holder name is made up of entirely digits like 123456 and holds no letters.

In general, checking if such cases exist can be carried out way faster than counting how many there are, or fetching all such cases: demo

Find all-numeric values in an alphanumeric field

4 Answers4