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 Go to next message
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 #603349 is a reply to message #603348] Fri, 13 December 2013 13:57 Go to previous messageGo to next message
BlackSwan
Messages: 22790
Registered: January 2009
Senior Member
design is FLAWED.
You should NEVER store multiple values in a single column.

>The solution can be long and clunky but it must be easy.
is "easy" defined as working SQL provided to you free of charge?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: How to find values from one record in a string from another record? [message #603352 is a reply to message #603348] Fri, 13 December 2013 14:09 Go to previous messageGo to next message
martijn
Messages: 278
Registered: December 2006
Location: Netherlands
Senior Member
Can you be a bit more specific about 'easy'. You even underlined it, so i think/hope you have some ideas around 'easy'.

Please supply the create and insert statement to build a 'demo' table ourselfs.
In that way we can work with what you have.
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 Go to previous messageGo to next message
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 #603356 is a reply to message #603355] Fri, 13 December 2013 14:53 Go to previous messageGo to next message
BlackSwan
Messages: 22790
Registered: January 2009
Senior Member
You should actually TEST all SQL before posting junk to this forum.
SQL> connect user1/user1
Connected.
SQL> 
CREATE TABLE table_1
(
csv varchar2(100)
);
SQL>   2    3    4  
Table created.

SQL> insert into table_1 (csv)
values
(1,3,5,7,9,11,13);  2    3  
insert into table_1 (csv)
            *
ERROR at line 1:
ORA-00913: too many values



do you know the difference between character strings and numbers in Oracle?
Re: How to find values from one record in a string from another record? [message #603357 is a reply to message #603356] Fri, 13 December 2013 15:00 Go to previous messageGo to next message
bws93222
Messages: 27
Registered: April 2009
Junior Member
sorry but as I said earlier I have no permissions to create tables--looks like I was missing quotes

insert into table_1 (csv)
values
('1,3,5,7,9,11,13');

[Updated on: Fri, 13 December 2013 15:00]

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 Go to previous messageGo to next message
Littlefoot
Messages: 19623
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 Go to previous messageGo to next message
martijn
Messages: 278
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 #603361 is a reply to message #603360] Fri, 13 December 2013 16:27 Go to previous messageGo to next message
bws93222
Messages: 27
Registered: April 2009
Junior Member
sorry no time to check now bu thx for your work--I'll resume this project monday and let you know
Re: How to find values from one record in a string from another record? [message #603449 is a reply to message #603361] Mon, 16 December 2013 12:30 Go to previous messageGo to next message
bws93222
Messages: 27
Registered: April 2009
Junior Member
Sorry but I was unable to get either to generate the results I needed.
Probably the problem is on my end with my attempt to implement your queries.
I'm afraid I am out of time now and must escalate internally.
Thanks for all your efforts--much appreciated!
Re: How to find values from one record in a string from another record? [message #603457 is a reply to message #603449] Mon, 16 December 2013 13:54 Go to previous messageGo to next message
martijn
Messages: 278
Registered: December 2006
Location: Netherlands
Senior Member
Out of curiousity, what were the problems you encountered?
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 Go to previous messageGo to next message
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 #604290 is a reply to message #604289] Fri, 27 December 2013 12:24 Go to previous messageGo to next message
Michel Cadot
Messages: 59143
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

where instr(','||t1.csv||',', ','||t2.AM_I_IN_CSV||',')>0;

Re: How to find values from one record in a string from another record? [message #604291 is a reply to message #604290] Fri, 27 December 2013 12:31 Go to previous messageGo to next message
bws93222
Messages: 27
Registered: April 2009
Junior Member
works like a charm!
Tha very much!!
Re: How to find values from one record in a string from another record? [message #604292 is a reply to message #604291] Fri, 27 December 2013 12:32 Go to previous messageGo to next message
bws93222
Messages: 27
Registered: April 2009
Junior Member
Thx
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2031
Registered: January 2010
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.
Re: How to find values from one record in a string from another record? [message #604294 is a reply to message #604293] Fri, 27 December 2013 12:38 Go to previous message
bws93222
Messages: 27
Registered: April 2009
Junior Member
yes, that makes a lot of sense--
Previous Topic: find no of times a column is queried on a table
Next Topic: TABLE TYPES with clustered
Goto Forum:
  


Current Time: Fri Sep 19 21:48:33 CDT 2014

Total time taken to generate the page: 0.11290 seconds