Re: how do i split a string

"Maxim Demenko" <mdemenko_at_gmail.com> wrote in message news:48F9F293.1080501_at_gmail.com...

> Bob Jones schrieb:
>> "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...

>>>>>> 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 --

>>>>>

>>>>>

>>>> 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.
>
> I never assured your replies were wrong or not what OP asked for.
> Just some thoughts on possible alternative approaches.
>

Ok, I just wasn't sure why you were providing an alternative solution to a different problem. Received on Sat Oct 18 2008 - 10:27:34 CDT