Re: How to split string variable

Dimitre's solution is good if your concern is layout (reporting). If you 
want to use the result for further processes (eg joins) you need to be a 
little wilder.
SQL> r
   1  select trim(substr(list, 1 + instr(list, ':', 1, rn),
   2             instr(list, ':', 1, rn + 1)
   3             - instr(list, ':', 1, rn) - 1)) stuff
   4  from (select a.list, rownum rn
   5       from (select ':' || '1:002:3:04:xyz:5:777:67:000:11' || ':' list
   6         from dual) a
   7*       connect by level < length(a.list) - length(replace(a.list, 
':', '')))

STUFF
--------------------------------------------------------------------------------
1
002
3
04
xyz
5
777
67
000
11

10 rows selected.



-- 
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>

On 11/25/2011 11:58 AM, Radoulov, Dimitre wrote:
> 11:57:50 SQL>  r
>     1  select
>     2    replace('1:002:3:04:xyz:5:777:67:000:11',':', chr(12))
>     3  from
>     4*   dual
>
> REPLACE('1:002:3:04:XYZ:5:777:
> ------------------------------
> 1
> 002
> 3
> 04
> xyz
> 5
> 777
> 67
> 000
> 11
>
>
> Regards
> Dimitre
>
>
>
> On 25/11/2011 11:34, Eriovaldo Andrietta 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
>
>
>



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


Other related posts: