example: Suppose I have a unique_id created from 4 parts i.e (college_id - dept_id -student_id-subject_id). So there must be 4-5 tables also respectively.
First, i have to extract separate parts from unique_id like college_id or dept_id and then have to get them validated from the respective tables?
This is the scenario. I need the complete explanation.
Parse the string out into columns
SQL> create table t (u varchar2(100));
Table created.
SQL> insert into t values ('ABC-EFGH-123-4567-789');
1 row created.
SQL>
SQL> with dash_pos as
2 ( select
3 u,
4 instr(u,'-') pos1,
5 instr(u,'-',1,2) pos2,
6 instr(u,'-',1,3) pos3,
7 instr(u,'-',1,4) pos4
8 from t
9 )
10 select
11 substr(u,1,pos1-1) part1,
12 substr(u,pos1+1,pos2-pos1-1) part2,
13 substr(u,pos2+1,pos3-pos2-1) part3,
14 substr(u,pos3+1,pos4-pos3-1) part4,
15 substr(u,pos4+1) part5
16 from dash_pos;
PART1 PART2 PART3 PART4 PART5
---------- ---------- ---------- ---------- ----------
ABC EFGH 123 4567 789
Then you can use each column to query into your respective tables.