Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Arun.

Asked: September 06, 2018 - 2:41 am UTC

Last updated: September 12, 2018 - 3:28 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Some records in First Name and Last Name are delimited by "/" and would like to split them into rows. CONNECT BY Clause works perfectly for one column. How do split the strings in multiple columns into rows as given below?

Sample Table :
CONTACT_ID, FIRST_NAME, ,LAST_NAME, ZIP_CODE
1, FName1/FName2/Fname3, LName1/LName2/LName3, 10101
2, AFNmae, ALName, 10101
3, BFName1/FName1, BLName1/LName1, 10123

Expected Results

CONTACT_ID, FIRST_NAME, ,LAST_NAME, ZIP_CODE
1, FName1, LName1, 10101
1, FName2, LName2, 10101
1, Fname3, LName3, 10101
2, AFNmae, ALName, 10101
3, BFName1, BLName1, 10123
3, FName1, LName1, 10123

create table t (

contact_id int,
first_name varchar2(200),
last_name varchar2(200),
zip_code int

);

insert into t values(1,'FName1/FName2/Fname3','LName1/LName2/LName3',10101);
insert into t values(2,'AFNmae','ALName',10101);
insert into t values(3,'BFName1/FName1','BLName1/LName1',10123);
commit;

and Connor said...

Assuming the pairings are always there, the standard method can be used to probe into each string, eg

SQL> create table t (
  2    contact_id int,
  3    first_name varchar2(200),
  4    last_name varchar2(200),
  5    zip_code int
  6  );

Table created.

SQL>
SQL> insert into t values(1,'FName1/FName2/Fname3','LName1/LName2/LName3',10101);

1 row created.

SQL> insert into t values(2,'AFNmae','ALName',10101);

1 row created.

SQL> insert into t values(3,'BFName1/FName1','BLName1/LName1',10123);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select
  2    t.contact_id,
  3    regexp_substr(t.first_name, '[^\/]+', 1, c1.column_value)  as fname,
  4    regexp_substr(t.last_name, '[^\/]+', 1, c1.column_value)  as lname
  5  from
  6    t,
  7    table(cast(multiset(select level from dual connect by  level <= length (regexp_replace(t.first_name, '[^\/]+'))  + 1) as sys.OdciNumberList)) c1;

CONTACT_ID FNAME                LNAME
---------- -------------------- --------------------
         1 FName1               LName1
         1 FName2               LName2
         1 Fname3               LName3
         2 AFNmae               ALName
         3 BFName1              BLName1
         3 FName1               LName1



Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library