Re: how do i split a string

From: Bob Jones <email_at_me.not>
Date: Sat, 18 Oct 2008 09:05:24 -0500
Message-ID: <R0mKk.2812$Ei5.695@flpi143.ffdc.sbc.com>

"Maxim Demenko" <mdemenko_at_gmail.com> wrote in message news:48F99429.8070204_at_gmail.com...

> Bob Jones schrieb:
>> "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
>>
>>
>>
>
> This would give the right answer, provided you are looking exactly for the 
> first and second occurency ( the same is valid for your regexp query).
> The sql statements by Michel are suitable for more generic case, when you 
> don't know, how many occurencies of searched sting there are.
> Another alternative which should as well work for earlier versions ( 
> except with clause of course)
>
> SQL> with t as (
>   2  select '12**5***899******55**' s from dual
>   3  )
>   4  -- End test data
>   5  select min(pos) pos,count(*) len from (
>   6  select t.s,
>   7         r.r,
>   8         instr(t.s, '*', 1, r) pos,
>   9         instr(t.s, '*', 1, r) - r shift
>  10    from t, (select rownum r from all_tables) r
>  11   where instr(t.s, '*', 1, r) > 0
>  12  )
>  13  group by shift
>  14  order by pos
>  15  ;
>
>        POS        LEN
> ---------- ----------
>          3          2
>          6          3
>         12          6
>         20          2
>
>
> Best regards
>
> Maxim

First and second occurrences were precisely what the OP was asking for. Also the result needs to be in one line. Received on Sat Oct 18 2008 - 09:05:24 CDT