Re: how do i split a string
From: Bob Jones <email_at_me.not>
Date: Fri, 17 Oct 2008 20:05:01 -0500
Message-ID: <cGaKk.2428$pr6.992@flpi149.ffdc.sbc.com>
> 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
Date: Fri, 17 Oct 2008 20:05:01 -0500
Message-ID: <cGaKk.2428$pr6.992@flpi149.ffdc.sbc.com>
"Maxim Demenko" <mdemenko_at_gmail.com> wrote in message
news:48F8EBB5.2030208_at_gmail.com...
> 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
In 9i or 8i, you can do something like this.
select instr(string,'*') Pos1,
nvl(length(substr(string,instr(string,'*'))),0) - nvl(length(ltrim(substr(string,instr(string,'*')),'*')),0) Len1,
instr(string,'*',instr(string,'*')+nvl(length(substr(string,instr(string,'*'))),0)
- nvl(length(ltrim(substr(string,instr(string,'*')),'*')),0)+1) Pos2, nvl(length(substr(ltrim(substr(string,instr(string,'*')),'*'), instr(ltrim(substr(string,instr(string,'*')),'*'),'*'))),0) - nvl(length(ltrim(substr(ltrim(substr(string,instr(string,'*')),'*'), instr(ltrim(substr(string,instr(string,'*')),'*'),'*')),'*')),0) Len2 from dual