Re: how do i split a string

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Sat, 18 Oct 2008 07:21:56 +0200
Message-ID: <48f97273$0$21907$426a74cc@news.free.fr>

"Mark D Powell" <Mark.Powell_at_eds.com> a écrit dans le message de news: c68247ec-e2f7-46db-9bed-7833bf3e437b_at_a1g2000hsb.googlegroups.com... 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, Maxim is right, I choosed this way as well as "case" instead of "decode" and factoring clause ("with") instead of inline view in "from" because (I think) it is easier to read and see the consecutive steps.

The minimum level for it to work is 8.1.6, here's the query for this version:

SQL> def in='12**5***'
SQL> select grp start_point, count(*) nb_stars   2 from (
  3 --
  4 select elem, max(flag) over (order by rn) grp   5 from (
  6 --

  7      select substr('&in',rn,1) elem, rn,
  8              decode(rn,
  9                     1, row_number() over (order by rn),
 10                     decode(decode(substr('&in',rn-1,1),'*','*','#'),
 11                            decode(substr('&in',rn,1),'*','*','#'), null,
 12                            row_number() over (order by rn)
 13                     )) flag
 14      from (
 15  --
 16        select rownum rn from all_objects where rownum <= length('&in')

 17 )))
 18 where elem = '*'
 19 group by grp
 20 order by grp
 21 /
START_POINT NB_STARS
----------- ----------

          3          2
          6          3

2 rows selected.

Regards
Michel Received on Sat Oct 18 2008 - 00:21:56 CDT