I want to show,

1. OraCleG
2. 10
3. @#$%

from the string "OraCle@10#$G%" in three different rows using oracle SQL code.

CodePudding user response：

Using regular expressions might be one option:

SQL> with test (col) as
  2    (select 'OraCle@10#$G%' from dual)
  3  select regexp_replace(col, '[^[:alpha:]]') letters,
  4         regexp_replace(col, '[^[:digit:]]') digits,
  5         regexp_replace(col, '[[:alnum:]]')  the_rest
  6  from test
  7  /

LETTERS DIGITS     THE_REST
------- ---------- ----------
OraCleG 10         @#$%

SQL>

If it must be 3 rows, then UNION them:

SQL> with test (col) as
  2    (select 'OraCle@10#$G%' from dual)
  3  select regexp_replace(col, '[^[:alpha:]]') value from test
  4  union all
  5  select regexp_replace(col, '[^[:digit:]]')       from test
  6  union all
  7  select regexp_replace(col, '[[:alnum:]]')        from test;

VALUE
-------
OraCleG
10
@#$%

SQL>