string aggregation [message #664676] |
Thu, 27 July 2017 06:08 |
|
praf1981
Messages: 3 Registered: July 2017
|
Junior Member |
|
|
I have two tables t1 and t2
t1 has a number column R which contains numbers
structure of t1 is as below:
RNUM R
1 4
2 9
3 2
4 7
5 10
6 5
7 1
8 3
9 8
10 6
t2 has a char column R which contains combination of numbers from table t1's R column
e.g:
I want to select only those records from t1 which is there in T2 table
in above example rows having value 2,4,9,3,6,8 should be selected.
|
|
|
|
Re: string aggregation [message #664713 is a reply to message #664676] |
Sun, 30 July 2017 23:42 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
There are a lot of ways to do this. I have provided 2 methods below.
-- test data:
SCOTT@orcl_12.1.0.2.0> select * from t1 order by rnum
2 /
RNUM R
---------- ----------
1 4
2 9
3 2
4 7
5 10
6 5
7 1
8 3
9 8
10 6
10 rows selected.
SCOTT@orcl_12.1.0.2.0> select * from t2 order by r1
2 /
R1
------------------------------
2,4,9
3,6,8
2 rows selected.
-- method1:
SCOTT@orcl_12.1.0.2.0> select t1.*
2 from t1, t2
3 where instr (',' || t2.r1 || ',', ',' || t1.r || ',') > 0
4 order by t1.rnum
5 /
RNUM R
---------- ----------
1 4
2 9
3 2
8 3
9 8
10 6
6 rows selected.
-- method2:
SCOTT@orcl_12.1.0.2.0> select t1.*
2 from t1,
3 (select regexp_substr (t2.r1, '[^,]+', 1, column_value) r1_num
4 from t2,
5 table
6 (cast
7 (multiset
8 (select rownum
9 from dual
10 connect by level <= regexp_count (t2.r1, ',') + 1)
11 as sys.odcinumberlist))) t3
12 where t1.r = t3.r1_num
13 order by t1.rnum
14 /
RNUM R
---------- ----------
1 4
2 9
3 2
8 3
9 8
10 6
6 rows selected.
|
|
|