Re: How to split string variable

From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Fri, 25 Nov 2011 12:17:32 +0100
Message-ID: <4ECF794C.6050309_at_roughsea.com>

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

Received on Fri Nov 25 2011 - 05:17:32 CST