Hi ..
create table t1(c clob)
;
insert into t1 values ('NAME;AGE;GENDER;DOB;PHONE;ZIP
AMIT;33;M;08/26/1982;1112221111;11223
SAM;30;M;08/26/1985;1113331111;11223
ANU;28;F;08/26/1987;1114441111;11223
MARK;25;M;08/26/1990;1115551111;11223
')
;
create table t2 (
name varchar2(5),
age number,
gender varchar2(1),
dob date,
phone varchar2(20),
zip varchar2(6)
);
/
1)I need to insert the data from t1 to t2 , in the t1 header column need not to insert in t2
2)some validation like if the clob column any spaces are there , need to validate that one( starting of clob column).
1) You need to go through a two step process.
First convert the single clob row into one row per line within it. I've done this by using the "connect by level" row generation method, counting how many newlines there are in the string and substracting one (because you have a header row).
With rows generated, just substring out each line. I've done this using instr to find the position of the nth occurrence of linefeed (chr(10)) plus one to find the start of each line. The length of each line is found by subtracting the nth position of chr(10) from the n+1 position of it (minus one).
select substr(c,
instr(c, chr(10), 1, level)+1,
instr(c, chr(10), 1, level+1)-instr(c, chr(10), 1, level)-1) val
from t1
connect by level <= regexp_count(c, chr(10))-1;
VAL
--------------------------------------------------
AMIT;33;M;08/26/1982;1112221111;11223
SAM;30;M;08/26/1985;1113331111;11223
ANU;28;F;08/26/1987;1114441111;11223
MARK;25;M;08/26/1990;1115551111;11223 Once you've go the individual rows, it's simply a matter of using string manipulation as above to get the field values, this time looking for semi colons instead of chr(10)s:
with rws as (
select substr(c,
instr(c, chr(10), 1, level),
instr(c, chr(10), 1, level+1)-instr(c, chr(10), 1, level)) val
from t1
connect by level <= regexp_count(c, chr(10))-1
)
select substr(val, 1, instr(val, ';', 1, 1)-1) name,
to_number(substr(val, instr(val, ';', 1, 1)+1, instr(val, ';', 1, 2)-1-instr(val, ';', 1, 1))) age,
substr(val, instr(val, ';', 1, 2)+1, instr(val, ';', 1, 3)-1-instr(val, ';', 1, 2)) gender,
to_date(substr(val, instr(val, ';', 1, 3)+1, instr(val, ';', 1, 4)-1-instr(val, ';', 1, 3)), 'mm/dd/yyyy') dob,
substr(val, instr(val, ';', 1, 4)+1, instr(val, ';', 1, 5)-1-instr(val, ';', 1, 4)) phone,
substr(val, instr(val, ';', 1, 5)+1) zip
from rws;
NAME AGE G DOB PHONE ZIP
---------- ---------- - ------------------- -------------------- ------
AMIT 33 M 26/08/1982 00:00:00 1112221111 11223
SAM 30 M 26/08/1985 00:00:00 1113331111 11223
ANU 28 F 26/08/1987 00:00:00 1114441111 11223
MARK 25 M 26/08/1990 00:00:00 1115551111 11223 You can then insert them with a insert into t2 select ...
2) not sure what you mean by that - please provide an example