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
>>
>>
>> --
>> //www.freelists.org/webpage/oracle-l
>>
>>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
