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