How to get data from table where text column contains only numeric.

There might be a time that you want to get data from the table. The condition is that only fetch the data that has the specific column as numeric only.

There are many way to do this, but one of the quick way that I can think of is as follow:

1. Replacing all character that is numeric in that columns with ''.
2. Fetch data from number 1 with the where condition that that specific column is null or ''.
3. Voila you get what you want.

Given the sample data in Table1 as below:

Table1

ColumnA	ColumnB
1	A123
2	B123
3	456
4	ABC

Here is the SQL code for

SELECT 
  * 
FROM (
   SELECT 
      ColumnA, ColumnB, replace(ColumnB, "all digit", "") as ColumnC
   FROM 
      Table1
)
WHERE 
   ColumnC = ""

Below is the code in Oracle

SELECT * FROM (
   SELECT 
      ColumnA, ColumnB, REGEXP_REPLACE(ColumnB,'[[:digit:]]', '') as ColumnC
   FROM 
      Table1
)
WHERE 
   ColumnC IS NULL;

Here is the code in MS Access

SELECT 
   * 
FROM (
   SELECT 
     ColumnA, ColumnB, regexp_replace(ColumnB, "[0-9]", "") as ColumnC
   FROM 
     Table1
)
WHERE 
   ColumnC = ""

In the same way, if you want to get data from table where text column contains only text (no number at all), just use "[a-z|A-Z]" or "[a-z]" as the pattern parameter (the second parameter) in your code.

And, the code in Oracle would be like

SELECT * FROM (
   SELECT 
      ColumnA, ColumnB, REGEXP_REPLACE(ColumnB,'[a-z]|[A-Z]', '') as ColumnC
   FROM 
      Table1
)
WHERE 
   ColumnC IS NULL;

And, here is how to do it in MS Access

SELECT 
   * 
FROM (
   SELECT 
     ColumnA, ColumnB, regexp_replace(ColumnB, "[a-z]", "") as ColumnC
   FROM 
     Table1
)
WHERE 
   ColumnC = ""

Noted:
To be able to use the code in MS Access, you need to add function regexp_replace in your vba code. This can be found at How to Replace String With Regular Expression in MS Access