Characters as Records 1

[nagornyi]

Let's say I have a string field in a table, so that if I do a simple select

select field from table

I get soomething like
ONE
TWO
THREE
ETC...
I need every selected string to be broken into characters, each character returned as new record. So, instead of getting the above I need to get this:
O
N
E
T
W
O
T
H
R
E
E
E
T
C
.
.
.
Is this possible with plain SQL?

 

[dnoeth]

create table nums (i int not null primary key);

Insert consecutive numbers (between 1 and the maximum numbers of chars) in that helper table.

Then it's a simple cross join:

select
substring(t.field from n.i for 1)
from table t, nums n
where
n.i >= 1 and
n.i <= char_length(t.field)
order by t.field, n.i;

Dieter

 

[nagornyi]

Thank you Dieter.
Not sure if Oracle SQL differs that much from the ANSI SQL, but on ORACLE I've gotten the code working like this:

select
substr(field,i,1)
from tbl t, nums n
where  i <= length(field)
order by field, i

I wonder if this can be done without creating any additional tables.
Again, for ORACLE, I had two variaties of this approach.
First. Instead of creating nums tablw we use some existing table with quaranteed number of records. Like:

select
substr(field,i,1)
from tbl t, (select rownum i from sometable) n
where  i <= length(field)
order by field, i

Second, this additional table we create on the fly right in the query:

select
substr(field,i,1)
from tbl t, 
(select 1 i from dual union
 select 2   from dual union
 select 3   from dual union
 select 4   from dual union
 select 5   from dual union
 select 6   from dual union
 select 7   from dual union
 select 8   from dual union
 select 9   from dual
) n
where  i <= length(field)
order by field, i

Even with these additional Oracle SQL capabilities, we still have limitations:
in the first case we still need some table to exist with quaranteeed number of recoreds, in the second case we need to generate all those records, so we need to know in advance how long the strings could be.
I wonder if there are other ways of doing this,