Home » SQL & PL/SQL » SQL & PL/SQL » Multiple occurances with INSTR???
Multiple occurances with INSTR??? [message #206432] Thu, 30 November 2006 00:52 Go to next message
deep_gh1
Messages: 13
Registered: October 2006
Location: India
Junior Member

Hi,

I have a table emp_cmc_addr which contains records like....

FIRSTNAME LASTNAME ADDRESS CITY STATE SSN
--------- -------- ---------------- ------- -------- ----------
sudip ghosh 83/first street howard ohio 512687458
ramesh ghosh 842/vine ave. losan ohio 758420012
abhishek das 33/elm st. paris new york 102254896
subhashis kar 89/elm st. paris new york 715621456
subhendu bhoumick 110/vine ave. losan ohio 325412653
sanjeev muckoul 56/first street howard ohio 512684791


and I tried a query like...

SQL> select ssn, INSTR(ssn,'8') from emp_cmc_addr;

the o/p comes like..


SSN INSTR(SSN,'8')
---------- --------------
512687458 5
758420012 3
102254896 7
715621456 0
325412653 0
512684791 5


But at a time it outputs only one occurance.

Is it possible to output multiple occurances using INSTR...???
That is it only shows the first possition of '8' i.e. possition
5, but there another occurance is in possition 9....

But when I use INSTR(ssn,'8',1,2) in query then it returns second occurance but

My question is "Is it possible to o/p both the occurances at the same time???

Any kind cooperation will be highly obliged..

Thanks & regards,

Randeep



Re: Multiple occurances with INSTR??? [message #206465 is a reply to message #206432] Thu, 30 November 2006 03:35 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Something like this perhaps?
SQL> with yourtable as ( select 512687458 t from dual union all
  2                      select 758420012 t from dual union all
  3                      select 102254896 t from dual union all
  4                      select 715621456 t from dual union all
  5                      select 325412653 t from dual union all
  6                      select 512684791 t from dual
  7                    )
  8  select distinct
  9         t
 10       , x
 11  from ( select t
 12              , instr(t,8,1,level) x
 13         from   yourtable y
 14         connect by level <= nvl(length(regexp_replace(t,'[^8]',null)),0)
 15       )
 16  where x > 0
 17  order by t, x
 18  /

         T          X
---------- ----------
 102254896          7
 512684791          5
 512687458          5
 512687458          9
 758420012          3


MHE
Previous Topic: ANSI Standards
Next Topic: regarding to database session
Goto Forum:
  


Current Time: Thu Dec 08 23:57:13 CST 2016

Total time taken to generate the page: 0.07866 seconds