Home » SQL & PL/SQL » SQL & PL/SQL » How to find values from one record in a string from another record? (TOAD PLSQL)
| How to find values from one record in a string from another record? [message #603348] |
Fri, 13 December 2013 13:48  |
bws93222
Messages: 27
Registered: April 2009
|
Junior Member |
|
|
HERE'S WHAT I'M WORKING WITH:
I have a comma-separated string as a value in a record in a table...
select fk_csv from TABLE1 where fk_csv = '1,3,8,11,23,55,87'
...and I have another set of records in a different table...
select fk_am_I_in_csv from TABLE2 where fk_am_I_in_csv in (1,2,3,4,5,6,7,8,9,10)
HERE'S WHAT I WANT TO DO:
I need an easy way to check which values in TABLE2.fk_am_I_in_csv appear
as individual comma-separated values in TABLE1.fk_csv
The solution can be long and clunky but it must be easy.
Any suggestions?
Thx.
|
|
|
|
|
|
|
|
| Re: How to find values from one record in a string from another record? [message #603355 is a reply to message #603352] |
Fri, 13 December 2013 14:40  |
bws93222
Messages: 27
Registered: April 2009
|
Junior Member |
|
|
by "easy" I mean something that someone who is not a programmer or DBA can grasp--
someone with maybe a year of basic SQL experience as an analyst
I don't have permissions to create table but here's what I can provide:
CREATE TABLE table_1
(
csv varchar2(100)
);
CREATE TABLE table_2
(
am_I_in_csv varchar2(100)
);
insert into table_1 (csv)
values
(1,3,5,7,9,11,13);
insert into table_2 (am_I_in_csv)
values
(1);
insert into table_2 (am_I_in_csv)
values
(2);
insert into table_2 (am_I_in_csv)
values
(3);
[Updated on: Fri, 13 December 2013 14:41] Report message to a moderator |
|
|
|
|
|
|
|
| Re: How to find values from one record in a string from another record? [message #603359 is a reply to message #603357] |
Fri, 13 December 2013 15:41 |
 |
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Based on data you provided, this could be one option:SQL> select * From table_1;
CSV
--------------------------------------------------------------
1,3,5,7,9,11,13
SQL> select * From table_2;
AM_I_IN_CSV
--------------------------------------------------------------
1
2
3
SQL> WITH t_csv AS ( SELECT REGEXP_SUBSTR (csv,
2 '[^,]+',
3 1,
4 LEVEL)
5 one_csv
6 FROM table_1
7 CONNECT BY REGEXP_SUBSTR (csv,
8 '[^,]+',
9 1,
10 LEVEL)
11 IS NOT NULL)
12 SELECT t2.am_i_in_csv
13 FROM table_2 t2
14 MINUS
15 SELECT t.one_csv
16 FROM t_csv t;
AM_I_IN_CSV
--------------------------------------------------------------
2
SQL>
|
|
|
|
| Re: How to find values from one record in a string from another record? [message #603360 is a reply to message #603357] |
Fri, 13 December 2013 15:44 |
martijn
Messages: 286
Registered: December 2006
Location: Netherlands
|
Senior Member |
|
|
How does this look?
SQL> select t2.AM_I_IN_CSV
from table_2 t2,
table_1 t1
where instr(t1.csv, t2.AM_I_IN_CSV)>0;
CSV AM_I_IN_CSV
-------------------- ------------
1,3,5,7,9,11,13 1
1,3,5,7,9,11,13 3
SQL> insert into table_1 (csv) values ('2,4,6');
1 row created.
SQL> commit;
Commit complete.
SQL> select t1.csv,
t2.AM_I_IN_CSV
from table_2 t2,
table_1 t1
where instr(t1.csv, t2.AM_I_IN_CSV)>0;
CSV AM_I_IN_CSV
-------------------- ------------
1,3,5,7,9,11,13 1
1,3,5,7,9,11,13 3
2,4,6 2
SQL>
(In the second example I added a row to the CSV table, to see if everything kept working)
|
|
|
|
|
|
|
|
|
|
| Re: How to find values from one record in a string from another record? [message #604289 is a reply to message #603457] |
Fri, 27 December 2013 12:09 |
bws93222
Messages: 27
Registered: April 2009
|
Junior Member |
|
|
Sorry, I didn't see your question until now. Actually, after trying again, I was able to get the following to work
select t1.csv,
t2.AM_I_IN_CSV
from table_2 t2,
table_1 t1
where instr(t1.csv, t2.AM_I_IN_CSV)>0;
However, there is an issue I would still need to work out.
When t2.AM_I_IN_CSV contains, say, a '1',
and t1.csv does not contain a single '1' but does contain an '11',
the sql will return a match which I don't want.
And ideas for a fix?
|
|
|
|
|
|
|
|
|
|
| Re: How to find values from one record in a string from another record? [message #604293 is a reply to message #604289] |
Fri, 27 December 2013 12:35 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
This is a standard task. you have a comma-separated value list, right?, so just wrap both with commas:
where instr(',' || t1.csv || ',',',' || t2.AM_I_IN_CSV || ',')>0;
Now:
SCOTT@pdborcl12 > select * from table_1;
CSV
--------------------
3,5,7,9,11,13
SCOTT@pdborcl12 > select * from table_2;
AM_I_IN_CSV
---------------
1
2
3
SCOTT@pdborcl12 > select t1.csv,t2.AM_I_IN_CSV
2 from table_2 t2,
3 table_1 t1
4 where instr(t1.csv, t2.AM_I_IN_CSV)>0;
CSV AM_I_IN_CSV
-------------------- ---------------
3,5,7,9,11,13 1
3,5,7,9,11,13 3
SCOTT@pdborcl12 > select t1.csv,t2.AM_I_IN_CSV
2 from table_2 t2,
3 table_1 t1
4 where instr(',' || t1.csv || ',',',' || t2.AM_I_IN_CSV || ',')>0;
CSV AM_I_IN_CSV
-------------------- ---------------
3,5,7,9,11,13 3
SCOTT@pdborcl12 >
SY.
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Apr 24 20:20:07 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
