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