以列方式显示'ORACLE'的 Oracle 查询

Question

如何将单词"ORACLE"显示为列式？

输出应该是这样的;

output
------
O
R
A
C
L
E

提前感谢！

Answer 1

任你选择：

select 'O
R
A
C
L
E' as "output"
from dual;
select replace('O R A C L E',' ',chr(10)) as "output"
from   dual;
select column_value as "output"
from   table(sys.dbms_debug_vc2coll('O','R','A','C','L','E'));

虽然也许你真正追求的是这个：

select regexp_replace('ORACLE','(.)', '1'||chr(10))
from   dual;

或者这个：

with demo (text) as
( select 'ORACLE' from dual )
select substr(text,rownum,1)
from   demo
connect by rownum <= length(text);

Answer 2

您可以使用SUBSTR和CONNECT BY

SELECT substr('ORACLE', LEVEL, 1) AS l
FROM DUAL CONNECT BY LEVEL < = LENGTH('ORACLE');

演示