Filter Alphabets from Oracle table-CodePudding

I have input like :-

Table 1	Table 2
A	4
B	5
C	6
1	X
2	Y
3	Z

And Output muse be

 Output
    A
    B
    C
    X
    Y
    Z

CodePudding user response：

One method uses a union followed by a filter:

SELECT val
FROM
(
    SELECT col1 AS val FROM yourTable
    UNION ALL
    SELECT col2 FROM yourTable
) t
WHERE REGEXP_LIKE(val, '^[A-Z] $')
ORDER BY val;

CodePudding user response：

If data really looks as you put it, a simple option is to use greatest function.

Sample data:

SQL> with test (col1, col2) as
  2    (select 'A', '4' from dual union all
  3     select 'B', '5' from dual union all
  4     select 'C', '6' from dual union all
  5     select '1', 'X' from dual union all
  6     select '2', 'Y' from dual union all
  7     select '3', 'Z' from dual
  8    )

Query:

  9  select greatest(col1, col2) result
 10  from test;

RESULT
----------
A
B
C
X
Y
Z

6 rows selected.

SQL>