Re: how do i split a string

On Oct 17, 3:47 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> Mark D Powell schrieb:
>
>
>
>
>
> > 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 --
>
> Mark, it can be hardly called enhancement, because this construct wasn't
> documented even in 11g, but you are correct, behaviour was changed
> (somewhere around 9i) - on 8.1.7.4 it gives ORA-01436: CONNECT BY loop
> in user data, but works quite well in Oracle 9.2.0.8 ( except the
> circumstance, that 9i sqlplus didn't return more than 1 row from dual,
> what can be easily faked by wrapping it into subquery). But that is not
> essential for Michel's solution, it will work with any row generator
> (like select rownum from all_tables) - i guess, he chose it just because
> it is compact. More important for his solution is availability of
> analytical functins, but they were introduced in 8.1.6 iirc.
>
> Best regards
>
> Maxim- Hide quoted text -
>
> - Show quoted text -

Maxim, thank you for pointing out that you could substitute a row generator for the connect by. I had not taken the time to study the query as that had jumped out at me. I had pointed out the first provided solution to the OP required 10g so that if the OP was using a prior release and Mike had tacked on to my post so I believed it was intended to be a prior 10g solution and as such was flawed. If I wasn't just checking my prior posts before heading out the door I might have been able to suggest the substitution of the row generator.

HTH -- Mark D Powell -- Received on Sat Oct 18 2008 - 13:11:52 CDT