Hi all.
I have the "CUSTOMER" table :
create table customer(first_name varchar2(128),middle_name
varchar2(128),last_name varchar2(128));
insert into customer values('Brian','','Frank');
And I need this output :
Brian 1
2
Frank 3
In order to get this output I implemented the following query:
SELECT REGEXP_SUBSTR(names, '[^,]+', 1, rownum)
names,REGEXP_SUBSTR(id, '[^,]+', 1, rownum) TYPE
FROM (
SELECT first_name||','||middle_name||','||
last_name names,
'1,2,3' id
FROM customer
)
CONNECT BY LEVEL <= LENGTH (REGEXP_REPLACE (names, '[^,]+')) + 1
However i get another output :
Brian 1
Frank 2
3
I can fixed it put a null the in most inner query :
SELECT REGEXP_SUBSTR(names, '[^,]+', 1, rownum)
names,REGEXP_SUBSTR(id, '[^,]+', 1, rownum) TYPE
FROM (
SELECT first_name||','||nvl(middle_name,'
')||','||last_name names,
'1,2,3' id
FROM customer
)
CONNECT BY LEVEL <= LENGTH (REGEXP_REPLACE (names, '[^,]+')) + 1
But can I correct it changing the regular expressions or another
aspect instead to introducing this NVL(I know that itcan be solved by
other way but I would like to user this query )?
Cheers,
P. Santana
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
