Re: how do i split a string
Date: Wed, 15 Oct 2008 17:07:40 +0200
Message-ID: <48f6073e$0$29162$426a74cc@news.free.fr>
"Mark D Powell" <Mark.Powell_at_eds.com> a écrit dans le message de news:
5c6a55cc-f094-4937-bc47-00e92d8cfa72_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
Received on Wed Oct 15 2008 - 10:07:40 CDT