Re: How to split string variable
- From: Eriovaldo Andrietta <ecandrietta@xxxxxxxxx>
- To: Ricardo Balieiro <rfbalieiro@xxxxxxxxx>
- Date: Fri, 25 Nov 2011 12:23:04 -0200
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@xxxxxxxxx>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@xxxxxxxxx> 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
>>
>>
>> --
>> //www.freelists.org/webpage/oracle-l
>>
>>
>>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
