Re: how do i split a string

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Fri, 17 Oct 2008 21:47:01 +0200
Message-ID: <48F8EBB5.2030208@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 Received on Fri Oct 17 2008 - 14:47:01 CDT

Original text of this message