Re: how do i split a string
From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Sat, 18 Oct 2008 20:50:41 +0200
Message-ID: <48FA3001.1080104@gmail.com>
>
> 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 --
>
>
>
>
Date: Sat, 18 Oct 2008 20:50:41 +0200
Message-ID: <48FA3001.1080104@gmail.com>
Mark D Powell schrieb:
> 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 --
>
>
>
>
You are welcome.
Best regards
Maxim Received on Sat Oct 18 2008 - 13:50:41 CDT