Re: how do i split a string

On Oct 15, 11:07 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> "Mark D Powell" <Mark.Pow..._at_eds.com> a écrit dans le message de news:
> 5c6a55cc-f094-4937-bc47-00e92d8cf..._at_u27g2000pro.googlegroups.com...
> On Oct 13, 9:43 pm, "Bob Jones" <em..._at_me.not> wrote:
>
>
>
>
>
> > <emdproduct..._at_hotmail.com> wrote in message
>
> >news:8a7a71fc-e783-4e0a-8a00-ae8d984c8024_at_d31g2000hsg.googlegroups.com...
>
> > > group,
>
> > > I have a record like this
> > > 12**5***
>
> > > I need to know the 1st occurance of *, and length of it
> > > the 2nd occurance of * and the length of it
>
> > > for example, for 12**5***, I need to get 3263
> > > meaning the 1st one start at 3, length is 2, the 2nd one start at 6,
> > > the length is 2
>
> > > Your help is highly appreciated
>
> > select regexp_instr('12**5***','\*+',1,1)
> > ||length(regexp_substr('12**5***','\*+',1,1))
> > ||regexp_instr('12**5***','\*+',1,2)
> > ||length(regexp_substr('12**5***','\*+',1,2))
> > from dual
>
> Emdproduct, Bob's answers requires that you have Oracle version 10g+.
> If you have an earlier version of Oracle a different solution will
> have to be sought.
>
> HTH -- Mark D Powell --
>
> ---------------------------------------------
>
> SQL> def in='12**5***'
> SQL> with
>   2    enumerating as (
>   3      select substr('&in',level,1) elem, level rn,
>   4              case
>   5                when    level = 1
>   6                     or decode(substr('&in',level-1,1),'*','*','#')
>   7                        != decode(substr('&in',level,1),'*','*','#')
>   8                  then row_number() over (order by level)
>   9              end flag
>  10      from dual
>  11      connect by level <= length('&in')
>  12    ),
>  13    grouping as (
>  14      select elem,
>  15              max(flag) over (order by rn) grp
>  16      from enumerating
>  17    )
>  18  select grp start_point, count(*) nb_stars
>  19  from grouping
>  20  where elem = '*'
>  21  group by grp
>  22  order by grp
>  23  /
> START_POINT   NB_STARS
> ----------- ----------
>           3          2
>           6          3
>
> 2 rows selected.
>
> Regards
> Michel- Hide quoted text -
>
> - Show quoted text -

Michel, isn't connect by level against dual a 10g enhancement?

• Mark D Powell --