Selecting records that only have a number in a VARCHAR column

Question

I am attempting a self join to find groups of records where an attribute is not the same length and the attribute is all numbers.

Here is the query I have so far:

SELECT * 
FROM MYTABLE t3
WHERE t3.GROUPID IN (SELECT t1.GROUPID
                     FROM MYTABLE t1, MYTABLE t2
                     WHERE t1.FILE_ID <> t2.FILE_ID
                       AND t1.GROUPID = t2.GROUPID
                       AND (length(t1.STR_FIELD) <> length(t2.STR_FIELD)))
  AND ORIG_GROUPID LIKE 'FRED_E%'
ORDER BY GROUPID ASC;

My table looks like this currently:

-----------------------------
|GROUPID |FILE_ID| STR_FIELD|
| 1      | 12314 | 101      |
| 1      | 54246 | 1011     |
| 2      | 75375 | 202      |
| 2      | 24664 | 202M     |
-----------------------------

In the above case, I'd only want GROUPID 1 returned:

-----------------------------
|GROUPID |FILE_ID| STR_FIELD|
| 1      | 12314 | 101      |
| 1      | 54246 | 1011     |
-----------------------------

How can I modify my query to allow this to happen?

Have a look at this: https://stackoverflow.com/questions/28819709/how-can-you-tell-if-a-value-is-not-numeric-in-oracle — Robert Kock, Apr 18 '19 at 13:31
Check this link [link](https://stackoverflow.com/questions/20887307/select-only-numbers-from-varchar-column) — Daniel Attard, Apr 18 '19 at 13:32
`REGEXP_LIKE` may be of use here as well. `WHERE t3.group_id NOT IN (SELECT DISTINCT group_id FROM MYTABLE WHERE REGEXP_LIKE(str_Field, '[^0-9]'))` for instance. That subquery would spit out all `group_id` where there was a non-numeric which would then get excluded from the main query. — JNevill, Apr 18 '19 at 13:42
[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**25 years** ago) and its use is discouraged — marc_s, Apr 18 '19 at 14:25

score 1 · Accepted Answer · answered Apr 18 '19 at 13:51

1

this will work:

select * from Table1 where regexp_like(STR_FIELD,'^\d+$');

check http://sqlfiddle.com/#!4/79c2f/5

rather this

select * from Table1 where regexp_like(STR_FIELD,'^\d*\d*$');

check http://sqlfiddle.com/#!4/5a8e9b/1

answered Apr 18 '19 at 13:51

Nikhil S

3,786
4
18
32

score 0 · Answer 2 · answered Apr 18 '19 at 14:27

Try this

  select * FROM MYTABLE   t3
    WHERE t3.GROUPID IN (
      SELECT t1.GROUPID
      FROM MYTABLE   t1, MYTABLE  t2
      WHERE t1.FILE_ID <> t2.FILE_ID
      AND t1.GROUPID = t2.GROUPID
      AND (length(t1.STR_FIELD)<> length(t2.STR_FIELD)))
      and ORIG_GROUPID like 'FRED_E%'
      and (VALIDATE_CONVERSION(t1.STR_FIELD AS NUMBER) = 1)
      and (VALIDATE_CONVERSION(t2.STR_FIELD AS NUMBER) = 1)
      order by GROUPID asc;

Selecting records that only have a number in a VARCHAR column

2 Answers2