Re: How to split string variable

From: Eriovaldo Andrietta <ecandrietta_at_gmail.com>
Date: Fri, 25 Nov 2011 12:23:04 -0200
Message-ID: <CAJdDhaOY4+aHe1gmyP7r7Ow1PH0wTArwYHcWDfdGw7gHATRHVA_at_mail.gmail.com>

Thanks !!
Issue solved.
Example 1:
select trim(substr(list, 1 + instr(list, ':', 1, rn),

             instr(list, ':', 1, rn + 1) - instr(list, ':', 1, rn) - 1)) stuff

    from (select a.list, rownum rn

         from (select ':' || '1:002:3:04:xyz:5:777:67:000:11' || ':' list
           from dual) a
         connect by level < length(a.list) - length(replace(a.list, ':',

'')));

Example 2:

select items.extract('/l/text()').getStringVal() item  from table(xmlSequence(
 extract(XMLType('<all><l>'||
 replace('1:002:3:04:xyz:5:777:67:000:11',':','</l><l>')||'</l></all>')  ,'/all/l'))) items;

Best regards
Eriovaldo.

On Fri, Nov 25, 2011 at 11:51 AM, Ricardo Balieiro <rfbalieiro_at_gmail.com>wrote:

> Dear friend,
>
> As a solution for your problem you could you the following query to split
> your string. You will have a row set that you can use either as view, or a
> stored function:
>
> select *
> from ( select case when row_number() over(order by loc) <> count(*)
> over() then
> substr(string,loc+1,(lead(loc,1) over(order by
> loc)-loc)-1)
> else
> substr(string,loc+1)
> end as splitted_string
> from ( select string
> , instr(v_string.string,':',1,rownum) as loc
> from ( select '1:002:3:04:xyz:5:777:67:000:11' as
> string -- #1) pass as parameter the string you want to split...
> from dual ) v_string
> , dba_tables
> -- 2) here you could use any table, just to make a row set...
> where rownum <= length(v_string.string) ) v_position )
> v_split
> where v_split.splitted_string is not null
>
> Regards,
>
> Ricardo.
>
>
>
>
> On Fri, Nov 25, 2011 at 8:34 AM, Eriovaldo Andrietta <
> ecandrietta_at_gmail.com> wrote:
>
>> Hi friends,
>>
>> I have this:
>>
>> 1:002:3:04:xyz:5:777:67:000:11
>>
>> and I want to extract this as result, using sql or regular expression:
>>
>> 1
>> 002
>> 3
>> 04
>> xyz
>> 5
>> 777
>> 67
>> 000
>> 11
>>
>> How can I do it ?
>>
>> Regards
>> Eriovaldo
>>
>>
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Nov 25 2011 - 08:23:04 CST