Skip to Main Content
  • Questions

  • Separate pipe delimited text into different field

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Peter.

Asked: May 09, 2006 - 11:47 am UTC

Last updated: August 09, 2006 - 10:09 am UTC

Version: 9i

Viewed 10K+ times! This question is

You Asked

I have the following text stored in the oracle 9i table and I want a sql so that I can get each pipe delimited field and give separate heading. This is stored in oracle table. I cannot use substr as it is not fixed width. Just pipe delimited. Please help.

U|/|Z|HALB|XSTR HF MR342|EA|/|/|1094|01|/|/|/|/|/|/|NORM|/|0000000000.000|G|0000000000.000|/|/|/|/|/|/|/|ZIMP|007-00700-0000|/| |/|/|TRANSSTR|/|/|/|/|/|/|/|/|/|/|000|/|/|3020|01|/|/|/|/|/|Z4|05082006| | |/|/|/|/|/|/|/|/|/|/|0000000000.000|/|/|/|1||/|01|NORM|/|/|/|000|/|/|/|/|/|/|/|/|/|/|/|/|/|/|/|/|Z2|/|/|0001|0001|/|/|/| |284003|^|/|/|0003| |/|/|/|/|/|/|/|/|/|/|/|/|/|/|/|/|/|X|/|X|U1||/|/||/|/|/|/|C28| | |1|X|1|/|/|1|| |/| |/|X40A|/|PD| |/|/|001|N|EX|/|/|/|/|/|/|/|/|3000|007-00700-0000|0000.00|007-00700-0000|NEW|F|/|40|100A|/|/|100A|/|/|/|/|003|/|001|/|/|/|/|/|/|/|/|/| |/|/|/|/|/|/|/|/|/|003|/|/| | |/|/|/|/|/|/|/|/|/|/|/|/|/|/|/|/|/|/|/|/|3000|100A|/|/|/|/|/|/|/|C| |0000000000.000|/|/|/|/|/|/|D| |000|/|/|/|/|/|/|001|001|/|/|/|/|/|/|/|001| |/|/|/|/|/|/| |/|-|/|/|/|/|/|/|Z|RAWH|/|/|S|00001|00000000000.00|/|/|/|/|X|/|X|/|Z00001|/|/||/|/|/|00000000001.00|/|X|0000| |Z01|Z05|Z06|X|X|X|X|X|X|X|/|X|X|X|/

and Tom said...

Of course you'll use substr - you just need to figure out where to start and stop.

ops$tkyte@ORA10GR2> create table t ( x varchar2(30) );

Table created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> insert into t values ( '1|2|3|4|5' );

1 row created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> select substr( x, instr(x,'|',1, nth )+1, instr( x, '|', 1, nth+1)-instr(x,'|',1,nth)-1 ) field
2 from (select rownum nth from all_objects where rownum <= 5 ), (select '|'||x||'|' x from t);

FIELD
--------------------------------
1
2
3
4
5



Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Amazing

Kamran, May 10, 2006 - 1:49 am UTC

Even it was hard for me to get how this query works, but it is simply excellent.

Need more help

GS, August 08, 2006 - 5:27 pm UTC

Hi,

I also have a similar situation which user has described. But the approach you gave, will list the "pipe delimited" characters in different "rows".
Can we split them into different columns. I mean:
string=1|2|3|4|5
Output:
FIELD1 FILED2 FIELD3 FILED4 FILED5
1 2 3 4 5

Tom Kyte
August 09, 2006 - 10:09 am UTC 

ops$tkyte%ORA9IR2> select substr( str2, instr( str2, '|', 1, 1 )+1, instr( str2,'|',1,2 )-instr( str2,'|',1,1 )-1 )c1,
  2         substr( str2, instr( str2, '|', 1, 2 )+1, instr( str2,'|',1,3 )-instr( str2,'|',1,2 )-1 )c2,
  3             /* ... */
  4         substr( str2, instr( str2, '|', 1, 5 )+1, instr( str2,'|',1,6 )-instr( str2,'|',1,5 )-1 )c5  5    from (
  6  select '|'||str||'|' str2
  7    from t
  8         )
  9  /
 
C1          C2          C5
----------- ----------- -----------
1           2           5

A generic pure SQL solution for the original question .

Frank Zhou, August 09, 2006 - 6:28 pm UTC 

    Here is a generic pure sql solution for the original question.

Thanks,

Frank

SQL> select * from t1;

X                                                                               
--------------------------------------------------------------------------------
        ID                                                                      
----------                                                                      
U|/|Z|HALB|XSTR                                                                 
         1                                                                      
                                                                                

SQL> SELECT SUBSTR(x,
  2                INSTR (x, '|', 1, LEVEL  ) + 1,
  3                INSTR (x, '|', 1, LEVEL+1) -
  4       INSTR (x, '|', 1, LEVEL) -1 ) strinig ,
  5       id
  6      FROM  ( select '|'||x||'|' AS x , id from t1 )
  7      CONNECT BY PRIOR id = id
  8      AND INSTR (x, '|', 1, LEVEL+1) > 0
  9      AND PRIOR dbms_random.string ('p', 10) IS NOT NULL
 10     ;

STRINIG                                                                         
--------------------------------------------------------------------------------
        ID                                                                      
----------                                                                      
U                                                                               
         1                                                                      
                                                                                
/                                                                               
         1                                                                      
                                                                                
Z                                                                               
         1                                                                      
                                                                                

STRINIG                                                                         
--------------------------------------------------------------------------------
        ID                                                                      
----------                                                                      
HALB                                                                            
         1                                                                      
                                                                                
XSTR                                                                            
         1                                                                      
                                                                                

SQL> spool off;

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.