Re: How to split string variable

  • From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
  • To: cichomitiko@xxxxxxxxx
  • Date: Fri, 25 Nov 2011 12:17:32 +0100

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: