Home -> Usenet -> c.d.o.server -> Re: how do i split a string

Re: how do i split a string

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Sat, 18 Oct 2008 16:28:35 +0200
Message-ID: <48F9F293.1080501@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...




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





I never assured your replies were wrong or not what OP asked for.
Just some thoughts on possible alternative approaches.



Best regards



Maxim
Received on Sat Oct 18 2008 - 09:28:35 CDT












Original text of this message