Re: how do i split a string

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Sat, 18 Oct 2008 09:45:45 +0200
Message-ID: <48F99429.8070204@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 Received on Sat Oct 18 2008 - 02:45:45 CDT