Home » SQL & PL/SQL » SQL & PL/SQL » Read a Comma separated Number String in Varchar2 field (Oracle 10g . Form 6i)
Read a Comma separated Number String in Varchar2 field [message #435837] |
Fri, 18 December 2009 06:08  |
rhnshk
Messages: 26 Registered: May 2008
|
Junior Member |
|
|
create table sinh
(ref_no varchar2(100));
insert into sinh values ('9');
insert into sinh values ('13');
insert into sinh values ('11,12');
select * from sinh;
REF_NO
-------
9
13
11,12
VAR RNO VARCHAR2(10);
EXECUTE :RNO := '11,8';
select 'x'
from sinh
WHERE instr(','||:RNO||',',','||REF_NO||',') > 0;
the above query returns rows for any combinations of numbers given for :RNO, which exists in table SINH Col. REF_NO. for example;
SCOTT@orcl>EXECUTE :RNO := '11,12';
SCOTT@orcl>select 'x' from sinh WHERE instr(','||:RNO||',',','||REF_NO||',') > 0;
'
-
x
SCOTT@orcl>EXECUTE :RNO := '11,9';
SCOTT@orcl>select 'x' from sinh WHERE instr(','||:RNO||',',','||REF_NO||',') > 0;
'
-
x
SCOTT@orcl>EXECUTE :RNO := '9,13';
SCOTT@orcl>select 'x' from sinh WHERE instr(','||:RNO||',',','||REF_NO||',') > 0;
'
-
x
x
SCOTT@orcl>EXECUTE :RNO := '13,12';
SCOTT@orcl>select 'x' from sinh WHERE instr(','||:RNO||',',','||REF_NO||',') > 0;
'
-
x
SCOTT@orcl>EXECUTE :RNO := '12,13';
SCOTT@orcl>select 'x' from sinh WHERE instr(','||:RNO||',',','||REF_NO||',') > 0;
'
-
x
but if i put some other number besides those present in REF_NO COL, for example, '11,8' it does not return any rows. Whereas it should return an 'X', because number 11 do exists in one of the rows of REF_NO
Please somebody help me modifying the above query.
tyvm
|
|
|
|
Re: Read a Comma separated Number String in Varchar2 field [message #435839 is a reply to message #435837] |
Fri, 18 December 2009 06:26   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
(Removing the query after the first one)
SQL> EXECUTE :RNO := '11,12';
PL/SQL procedure successfully completed.
SQL> with
2 lines as (select level line from dual connect by level < 10),
3 data as (select ','||ref_no||',' ref_no from sinh),
4 the_values as (
5 select distinct
6 substr(ref_no,
7 instr(ref_no,',',1,line)+1,
8 instr(ref_no,',',1,line+1)-instr(ref_no,',',1,line)-1
9 ) ref_no
10 from data, lines
11 where line < length(ref_no)-length(replace(ref_no,','))
12 )
13 select distinct 'x'
14 from the_values
15 where instr(','||:RNO||',',','||REF_NO||',') > 0
16 /
'
-
x
1 row selected.
SQL> EXECUTE :RNO := '11,9';
PL/SQL procedure successfully completed.
SQL> with
...
16 /
'
-
x
1 row selected.
SQL> EXECUTE :RNO := '9,13';
PL/SQL procedure successfully completed.
SQL> with
...
16 /
'
-
x
1 row selected.
SQL> EXECUTE :RNO := '13,12';
PL/SQL procedure successfully completed.
SQL> with
...
16 /
'
-
x
1 row selected.
SQL> EXECUTE :RNO := '12,13';
PL/SQL procedure successfully completed.
SQL> with
...
16 /
'
-
x
1 row selected.
SQL> EXECUTE :RNO := '11,8';
PL/SQL procedure successfully completed.
SQL> with
...
16 /
'
-
x
1 row selected.
Regards
Michel
|
|
|
Re: Read a Comma separated Number String in Varchar2 field [message #435842 is a reply to message #435839] |
Fri, 18 December 2009 06:39   |
rhnshk
Messages: 26 Registered: May 2008
|
Junior Member |
|
|
thats fantastic Mr. Michel, i am glad to see your query taking care for numbers specified outside the values of REF_NO Col.
i dont know much about using "WITH", could you please re-write it without using the "WITH.." clause..
it will be easier for me to put it in my Form(6i), in a cursor.
Bundle of Thanks & Regards
|
|
|
|
Re: Read a Comma separated Number String in Varchar2 field [message #435854 is a reply to message #435848] |
Fri, 18 December 2009 07:39   |
rhnshk
Messages: 26 Registered: May 2008
|
Junior Member |
|
|
SCOTT@orcl>select distinct 'x'
2 from ((select ','||ref_no||',' ref_no from sinh),
3 select distinct 'x'
4 from ( select distinct
5 substr(ref_no,
6 instr(ref_no,',',1,line)+1,
7 instr(ref_no,',',1,line+1)-instr(ref_no,',',1,line)-1
8 ) ref_no
9 select ','||ref_no||',' ref_no from sinh
10 ),
11 (
12 select level line from dual connect by level < 10
13 )
14 );
from ((select ','||ref_no||',' ref_no from sinh),
*
ERROR at line 2:
ORA-00907: missing right parenthesis
definetly missing somthing to be picked.
please help Mr. Michel.
Regards.
|
|
|
|
Re: Read a Comma separated Number String in Varchar2 field [message #435863 is a reply to message #435856] |
Fri, 18 December 2009 08:11   |
rhnshk
Messages: 26 Registered: May 2008
|
Junior Member |
|
|
after doing that query did return 'X', but one segment is ignored
SCOTT@orcl>select distinct 'x'
2 from ( -- code of "the_values"
3 select distinct
4 substr(ref_no,
5 instr(ref_no,',',1,line)+1,
6 instr(ref_no,',',1,line+1)-instr(ref_no,',',1,line)-1
7 ) ref_no
8 from ( -- code of "data"
9 select ','||ref_no||',' ref_no from sinh
10 ),
11 ( -- code of "lines"
12 select level line from dual connect by level < 10
13 )
14 );
'
-
x
the :RNO subquery which actually test different values, that too should be inserted somewhere.
13 select distinct 'x'
14 from the_values
15 where instr(','||:RNO||',',','||REF_NO||',') > 0
16 /
|
|
|
|
Re: Read a Comma separated Number String in Varchar2 field [message #435869 is a reply to message #435865] |
Fri, 18 December 2009 08:32   |
rhnshk
Messages: 26 Registered: May 2008
|
Junior Member |
|
|
Mr. Michel,
Yes, it is a sort of YES/NO check.
query returning 'x' means YES and No Rows for NO is understood.
if you refer to your first reply, there we tested the query by entering different values for :RNO and your query succesfully retuned rows.
SQL> EXECUTE :RNO := '11,12';
SQL> EXECUTE :RNO := '9,13';
SQL> EXECUTE :RNO := '13,12';
SQL> EXECUTE :RNO := '12,13';
SQL> EXECUTE :RNO := '11,8';
whereas here in the following query, i didnt supply any values, checking the existence of :RNO values in REF_NO.
SCOTT@orcl>select distinct 'x'
2 from ( -- code of "the_values"
3 select distinct
4 substr(ref_no,
5 instr(ref_no,',',1,line)+1,
6 instr(ref_no,',',1,line+1)-instr(ref_no,',',1,line)-1
7 ) ref_no
8 from ( -- code of "data"
9 select ','||ref_no||',' ref_no from sinh
10 ),
11 ( -- code of "lines"
12 select level line from dual connect by level < 10
13 )
14 );
'
-
x ,
simply returning a row, for what it is getting satisfied?
where is the Line No. 15 from your first query;
where instr(','||:RNO||',',','||REF_NO||',') > 0
this should be somewhere, am i clear.
for example my table has
REF_NO
-------
9
13
11,12
now if i test for, EXECUTE :RNO := '15';
this query (in this post) still returns a row
'
-
x
which it shouldn't, as 15 does not exist in the list.
The confusion is being creatd by myself, as i am unable to remove the "WITH" clause. otherwise its a perfect solution, it seems.
[Updated on: Fri, 18 December 2009 08:50] Report message to a moderator
|
|
|
|
Re: Read a Comma separated Number String in Varchar2 field [message #435879 is a reply to message #435873] |
Fri, 18 December 2009 09:01  |
rhnshk
Messages: 26 Registered: May 2008
|
Junior Member |
|
|
I GOT IT..
Thank you so much Mr. Michel. the query should be like this with out "WITH"
select distinct 'x'
from (
select distinct
substr(ref_no,
instr(ref_no,',',1,line)+1,
instr(ref_no,',',1,line+1)-instr(ref_no,',',1,line)-1
) ref_no
from ( -- code of "data"
select ','||ref_no||',' ref_no from sinh
),
(
select level line from dual connect by level < 10
)
)
where instr(','||:RNO||',',','||REF_NO||',') > 0;
i missed the last line.
[Updated on: Fri, 18 December 2009 09:29] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Mon Feb 17 22:19:05 CST 2025
|