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 Go to next message
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 #435838 is a reply to message #435837] Fri, 18 December 2009 06:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You have to split the column values into several rows.

Regards
Michel

[Updated on: Fri, 18 December 2009 06:28]

Report message to a moderator

Re: Read a Comma separated Number String in Varchar2 field [message #435839 is a reply to message #435837] Fri, 18 December 2009 06:26 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #435848 is a reply to message #435842] Fri, 18 December 2009 07:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
With is (here) just a confortable way to write the subquery.
It is:
select distinct 'x' 
from ( -- code of "the_values"
       select distinct ... "the_values" select list
       from ( -- code of "data"
              select ','||ref_no||',' ref_no from sinh
             ),
            ( -- code of "lines"
              select level line from dual connect by level < 10
            )
      )
/

Regards
Michel
Re: Read a Comma separated Number String in Varchar2 field [message #435854 is a reply to message #435848] Fri, 18 December 2009 07:39 Go to previous messageGo to next message
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
Sad

definetly missing somthing to be picked.
please help Mr. Michel.

Regards.
Re: Read a Comma separated Number String in Varchar2 field [message #435856 is a reply to message #435854] Fri, 18 December 2009 07:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Just take the last query I posted and replace "distinct ... "the_values" select list" by the select list that was in the_values subquery.

Regards
Michel


Re: Read a Comma separated Number String in Varchar2 field [message #435863 is a reply to message #435856] Fri, 18 December 2009 08:11 Go to previous messageGo to next message
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 #435865 is a reply to message #435863] Fri, 18 December 2009 08:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
after doing that query did return 'X', but one segment is ignored

What does this mean? What kind of a segment are you refering?

Quote:
the :RNO subquery which actually test different values, that too should be inserted somewhere.

Which values?

The query returns 'X' is there is a value matching of the input values and no row if there is none.

Regards
Michel


Re: Read a Comma separated Number String in Varchar2 field [message #435869 is a reply to message #435865] Fri, 18 December 2009 08:32 Go to previous messageGo to next message
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 #435873 is a reply to message #435869] Fri, 18 December 2009 08:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
If I refer to your first post you output "x" when there is something matching.
As there is no (clear) requirement of what the output should be, I assume for my query a YES/NO feature.
If it is not this, post a CLEAR requirement: from your data and examples of RNO what should be the result?
If you want several "x" then just remove "distinct".

Regards
Michel
Re: Read a Comma separated Number String in Varchar2 field [message #435879 is a reply to message #435873] Fri, 18 December 2009 09:01 Go to previous message
rhnshk
Messages: 26
Registered: May 2008
Junior Member
I GOT IT.. Smile

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

Previous Topic: Please resolve below SQL Query
Next Topic: Join ? left outer join ?
Goto Forum:
  


Current Time: Mon Feb 17 22:19:05 CST 2025