-
Sql query
I have an input string ('a','b','c')
Is there any way i can get the follg o/p using SQL (8i or 91 or 10g) (No PL/SQL)
'a'
'b'
'c'
I need the result to be displayed as 3 records
instead of 1 record
The no of columns in string may vary
Thnx in advance
-
A strange requirement for no PL/SQL, but here's a SQL-only solution, though you will need a SQL collection type. What I've done is to determine the number of delimited elements in each string and then generate a row per element "on the fly". Using a combination of INSTR, SUBSTR and the element number, it is possible to parse out the individual values.
Note that in the example, I've commented out a REGEXP_REPLACE function ( 10g ). Including this was bombing my session. YMMV.
Code:
SQL> CREATE TABLE t
2 ( str VARCHAR2(30) );
Table created.
SQL>
SQL> INSERT INTO t VALUES ( 'X,Y,Z' );
1 row created.
SQL> INSERT INTO t VALUES ( 'XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG' );
1 row created.
SQL>
SQL> CREATE TYPE number_ntt AS TABLE OF NUMBER;
2 /
Type created.
SQL>
SQL> WITH ilv AS
2 (
3 SELECT str || ',' AS str
4 , LENGTH(
5 TRANSLATE(
6 UPPER( str ), ',ABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890!', ',' ) ) + 1 AS no_of_elements
7 --, LENGTH( REGEXP_REPLACE( s, '[[:alnum:]]' ) ) + 1 AS no_of_elements
8 FROM t
9 )
10 SELECT RTRIM(str,',') AS original_string
11 , SUBSTR( str, start_pos, (next_pos-start_pos) ) AS single_element
12 , element_no
13 FROM (
14 SELECT ilv.str
15 , nt.column_value AS element_no
16 , INSTR(
17 ilv.str,
18 ',',
19 DECODE( nt.column_value, 1, 0, 1 ),
20 DECODE( nt.column_value, 1, 1, nt.column_value-1 ) ) + 1 AS start_pos
21 , INSTR(
22 ilv.str,
23 ',',
24 1,
25 DECODE( nt.column_value, 1, 1, nt.column_value ) ) AS next_pos
26 FROM ilv
27 , TABLE(
28 CAST(
29 MULTISET(
30 SELECT ROWNUM FROM dual CONNECT BY ROWNUM < ilv.no_of_elements
31 ) AS number_ntt ) ) nt
32 );
ORIGINAL_STRING SINGLE_ELEMENT ELEMENT_NO
------------------------------- ------------------------------- ----------
X,Y,Z X 1
X,Y,Z Y 2
X,Y,Z Z 3
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG XXX 1
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG Y 2
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG ZZ 3
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG AAAAA 4
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG B 5
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG CCC 6
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG D 7
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG E 8
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG F 9
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG GGG 10
13 rows selected.
Regards
Adrian
-
Hi,
If you have a table containing at least as many records as the input values, you can use the following query
Code:
select substr(:x,begin,end-begin)
from (select pos+1 begin,nvl(lead(pos) over (order by pos),length(:x)+1) end
from (select instr(:x,',',1,rownum) pos
from dba_objects)
where rownum <= length(:x)-length(replace(:x,','))+1
);
SQL> exec :x := 'a,b,c,d,e,f,g,h'
PL/SQL-procedure is geslaagd.
SQL> select substr(:x,begin,end-begin)
2 from (select pos+1 begin,nvl(lead(pos) over (order by pos),length(:x)+1) end
3 from (select instr(:x,',',1,rownum) pos
4 from dba_objects)
5 where rownum <= length(:x)-length(replace(:x,','))+1
6 );
SUBSTR(:X,BEGIN,END-BEGIN)
--------------------------------------------------------------------------------
a
b
c
d
e
f
g
h
8 rijen zijn geselecteerd.
Regards,
Arian
-
Arian,
I think the OP has a whole table full of these strings and as such, you need to consider a join.
Regards
Adrian
-
Adrian,
I don't see why I need a join. I just need a table with sufficient rows. And in 9i and up, I could use dual with a connect by clause to generate sufficient rows.
Regards,
Arian
-
If you look at my example, I have a table "T" with more than one string of different sizes, as specified by the OP. You need to consider how you would replace your :x bind variable with a SELECT from the table of strings. It's less simple when you need to extract the elements from more than one string.
Regards
Adrian
-
Ok. I didn't understand from the question that there is a table.
Unfortunately, I can't run your query, since we're still on 8i. So I had to try something else.
Code:
select str, substr(str,begin,end-begin) element
from (select str,pos+1 begin,nvl(lead(pos) over (partition by tri order by pos),length(str)+1) end
from (select a.str, instr(a.str,',',1,o.rn) pos, a.tri
from (select t.str,length(t.str)-length(replace(t.str,','))+1 num_el,t.rowid tri
from t
) a
, (select rownum rn, object_id
from dba_objects) o
where o.rn<=a.num_el
)
);
Code:
SQL> CREATE TABLE t
2 ( str VARCHAR2(30) );
Tabel is aangemaakt.
SQL> INSERT INTO t VALUES ( 'X,Y,Z' );
1 rij is aangemaakt.
SQL> INSERT INTO t VALUES ( 'XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG' );
1 rij is aangemaakt.
SQL> select str, substr(str,begin,end-begin) element
2 from (select str,pos+1 begin,nvl(lead(pos) over (partition by tri order by pos),length(str)+1)
end
3 from (select a.str, instr(a.str,',',1,o.rn) pos, a.tri
4 from (select t.str,length(t.str)-length(replace(t.str,','))+1 num_el,t.rowid tri
5 from t
6 ) a
7 , (select rownum rn, object_id
8 from dba_objects) o
9 where o.rn<=a.num_el
10 )
11 );
STR ELEMENT
------------------------------ ------------------------------
X,Y,Z X
X,Y,Z Y
X,Y,Z Z
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG XXX
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG Y
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG ZZ
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG AAAAA
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG B
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG CCC
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG D
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG E
STR ELEMENT
------------------------------ ------------------------------
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG F
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG GGG
13 rijen zijn geselecteerd.
Regards,
Arian
Edit: I realized later, that I used dba_objects as a matter of habit. Replace that with all_objects. In your case all_objects, or a custom-made table with sufficient rows would suffice. From 9i on, dual with a connect by is a safe replacement.
Last edited by Alien; 07-13-2005 at 01:21 PM.
Reason: Creatures of habit
-
To Adrian: Wonder about 9i
Hi Adrian,
Hope you'll be able to follow up on this reply.
I tested your script on my own server. And I received an ORA-3113.
So I was wondering what versions you used to test your script. I tested on 8.1.7.4 and 9.2.0.5.
If you managed to run it on <=9.2.0.5, I'd like to file a bug with Oracle Support.
Regards,
Arian
P.S. Wonder when people get confused about our names?
Last edited by Alien; 07-13-2005 at 02:22 PM.
-
Arian,
The query posted above was tested on 10.1.0.3 only, but it is all 9i syntax. I was getting the ORA-3113 when I included the REGEXP_REPLACE. But, I have an 8i version that I also wrote and forgot to post ( the OP asked for all versions ). You could perhaps test this:-
Code:
SELECT RTRIM( str,',' ) AS original_string
, SUBSTR( str, start_pos, (next_pos-start_pos) ) AS single_element
, element_no
FROM (
SELECT ilv.str
, nt.column_value AS element_no
, INSTR(
ilv.str,
',',
DECODE( nt.column_value, 1, 0, 1 ),
DECODE( nt.column_value, 1, 1, nt.column_value-1 ) ) + 1 AS start_pos
, INSTR(
ilv.str,
',',
1,
DECODE( nt.column_value, 1, 1, nt.column_value ) ) AS next_pos
FROM (
SELECT str || ',' AS str
, LENGTH(
TRANSLATE(
UPPER( str ),
',ABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890', ',' ) ) + 1 AS no_of_elements
FROM t
) ilv
, TABLE(
CAST(
MULTISET(
SELECT ROWNUM FROM all_objects WHERE ROWNUM <= ilv.no_of_elements
) AS number_ntt ) ) nt
)
Regards,
Arian, I mean, Adrian...
-
Just for fun, try this (requires 9i, tested in 9.2.0.1):
Code:
SELECT str, EXTRACTVALUE(xt.column_value,'e') AS name
FROM t
, TABLE(XMLSEQUENCE
( EXTRACT
( XMLTYPE('<coll><e>' ||
REPLACE(t.str,',','</e><e>') ||
'</e></coll>')
, '/coll/e') )) xt;
Last edited by WilliamR; 07-17-2005 at 05:48 PM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|