On Sep 1, 2:26 pm, Santana <paulito.sant...@gmail.com> wrote:
> 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

Not that I know of -- nothing in the regular expression syntax matches
NULL which is why, I think, your original output wasn't as you
expected.  If you want the output  you posted it will require
introduction of NVL().


David Fitzjarrell

-- 
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

Reply via email to