Re: how do i split a string

From: Bob Jones <email_at_me.not>
Date: Fri, 17 Oct 2008 20:05:01 -0500
Message-ID: <cGaKk.2428$pr6.992@flpi149.ffdc.sbc.com>

"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
Received on Fri Oct 17 2008 - 20:05:01 CDT

Original text of this message