Home » SQL & PL/SQL » SQL & PL/SQL » Spliting string to column!
Spliting string to column! [message #302941] Wed, 27 February 2008 07:11 Go to next message
etl_prasad
Messages: 44
Registered: June 2005
Location: Scotland
Member
Hi All,

It may be another pivot but please help me out by giving your suggestions.

i have table like

col1 col2 col3 col4 col5 col6
'>abcccc>col1>col2>col3>col4>col5>col6


want to update this table by splitting the string usign the '>" as delimiter,, my out put for each column like

col1 col2 col3 col4 col5 col6
'>abcccc>col1>col2>col3>col4>col5>col6 col1 col2 col3 col4 col5


here i can have one string to N number but i need to split till 6 levels only

I tried using SUBSTR and INSTR functions in SELECT/update statement but i want to build code in PL/SQL please suggest me work around to solve this
Re: Spliting string to column! [message #302945 is a reply to message #302941] Wed, 27 February 2008 07:18 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Could you please post a clear example. I am sorry to tell you I cannot understand what you are trying to say. When you say
Quote:
i have table like

col1 col2 col3 col4 col5 col6


This is of no good. Explain what your table looks like using a describe statement. How the data is
Quote:
>abcccc>col1>col2>col3>col4>col5>col6
Again this is not a good example. You should give us atleast few number of rows to play with (with the insert statements). Then explain what you have tried. Atleast you should have tried some sql to achieve your output. Without these information it will be pretty difficult for anybody to help you. Last but not least read the forum guidelines ( I am tired of typing this). Format your post and before posting it hit the preview button and ask for yourself is it easy to read, is it clear, is it self explanatory, is it having any ambiguity then please post it.

Apologies for the ranting.

Regards

Raj
Re: Spliting string to column! [message #302958 is a reply to message #302941] Wed, 27 February 2008 07:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's a staring point: http://www.orafaq.com/forum/m/285231/102589/?srch=instr+substr+dual#msg_285231

Regards
Michel
Re: Spliting string to column! [message #302961 is a reply to message #302941] Wed, 27 February 2008 07:49 Go to previous messageGo to next message
etl_prasad
Messages: 44
Registered: June 2005
Location: Scotland
Member
I am really sorry for not been clear...

here is my source table

Quote:
COL1 COL2 COL3 COL4 COL5 COL6
>justice>carlosd>cellswor>jheadley
>justice>carlosd>cellswor>ggalster>csyoon
>justice>carlosd>jsauer
>justice
>justice>carlosd>amalani>mlewis>bteasdal>valotto


i have data in first column only,, i want to load the other columns based on data in first column.

COL1			                         COL2       COL3      COL4       COL5      COL6  
>justice>carlosd>cellswor>jheadley               carlosd    cellswor  jheadley 
>justice>carlosd>cellswor>ggalster>csyoon        carlosd    cellswor  jheadley 
>justice>carlosd>jsauer                          carlosd    cellswor  jheadley    
>justice
>justice>carlosd>amalani>mlewis>bteasdal>valotto carlosd    amalani    mlewis    bteasdal  valotto   


i have used following sql to solve this

select 
case when instr(COL1,'>',1,3)=0 then substr(COL1
,instr(COL1,'>',1,2)+1) else 
substr(COL1,instr(COL1,'>',1,2)+1,
(instr(COL1,'>',1,3)-1)- instr(COL1,'>',1,2)) end as COL1
,case when instr(COL1,'>',1,4)=0 then 'null' else 
substr(COL1,instr(COL1,'>',1,3)+1,
(instr(COL1,'>',1,4)-1)- instr(COL1,'>',1,3)) end as COL2
,case when instr(COL1,'>',1,5)=0 then 'null' else 
substr(COL1,instr(COL1,'>',1,4)+1,
(instr(COL1,'>',1,5)-1)- instr(COL1,'>',1,4)) end as COL3
,case when instr(COL1,'>',1,6)=0 then 'null' else 
substr(COL1,instr(COL1,'>',1,5)+1,
(instr(COL1,'>',1,6)-1)- instr(COL1,'>',1,5)) end as COL4
,case when instr(COL1,'>',1,7)=0 then 'null' else 
substr(COL1,instr(COL1,'>',1,6)+1,
(instr(COL1,'>',1,7)-1)- instr(COL1,'>',1,6)) end as COL5
,case when instr(COL1,'>',1,8)=0 then 'null' else 
substr(COL1,instr(COL1,'>',1,7)+1,
(instr(COL1,'>',1,8)-1)- instr(COL1,'>',1,7)) end as COL6
from TABLE_NAME  


NOW i ned to solve this in PL/SQL but i am not that much good engough in PL/SQL

I believe it might understand now ,,

Apologies for my previous inclear post

[Updated on: Wed, 27 February 2008 07:51] by Moderator

Report message to a moderator

Re: Spliting string to column! [message #302963 is a reply to message #302961] Wed, 27 February 2008 07:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you want to do it in PL/SQL if you know how to do it in SQL?
By the way, if you know how to do in SQL, you know jow to do it in PL/SQL, just put your SQL query in PL/SQL.

Or is this just a PL/SQL homework?

Regards
Michel
Re: Spliting string to column! [message #302967 is a reply to message #302941] Wed, 27 February 2008 08:09 Go to previous messageGo to next message
etl_prasad
Messages: 44
Registered: June 2005
Location: Scotland
Member
I have written in SQL but my client wants smart code in PL/SQL,,
i am new bie to PL/SQL programs. thats why i am expecting help to write code.


Re: Spliting string to column! [message #302973 is a reply to message #302967] Wed, 27 February 2008 08:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no smarter PL/SQL code than a SQL query.

Regards
Michel
Re: Spliting string to column! [message #302978 is a reply to message #302941] Wed, 27 February 2008 08:27 Go to previous messageGo to next message
etl_prasad
Messages: 44
Registered: June 2005
Location: Scotland
Member
i want to write Procedure/Function which contain one SUBSTR which loops for every record.. where as in SQL i have used more than one substing function which looking very clumsy and really taking time to debug the code...
Re: Spliting string to column! [message #303009 is a reply to message #302978] Wed, 27 February 2008 10:08 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
There is a str2tbl function around, that does something similar to that, have a look at listing 2 here :

http://www.oracle.com/technology/oramag/oracle/07-mar/o27asktom.html

You should be able to change it so that it doesn't return the str2tblType, but either a rowtype or a single column field corresponding to an additional parameter (1..n), according to your needs.

You won't be able to return multiple columns with a single function call in SQL, though.
Re: Spliting string to column! [message #303012 is a reply to message #303009] Wed, 27 February 2008 10:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is also the standard DBMS_UTILITY.COMMA_TO_TABLE.

Regards
Michel
Re: Spliting string to column! [message #303035 is a reply to message #303012] Wed, 27 February 2008 12:47 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link

http://www.oratechinfo.co.uk/delimited_lists_to_collections.html

Regards

Raj

Re: Spliting string to column! [message #303299 is a reply to message #302978] Thu, 28 February 2008 15:53 Go to previous message
Barbara Boehmer
Messages: 8631
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> SELECT * FROM table_name
  2  /

COL1                                             COL2     COL3     COL4     COL5     COL6
------------------------------------------------ -------- -------- -------- -------- --------
>justice>carlosd>cellswor>jheadley
>justice>carlosd>cellswor>ggalster>csyoon
>justice>carlosd>jsauer
>justice
>justice>carlosd>amalani>mlewis>bteasdal>valotto

SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION list_element
  2  	(p_string    VARCHAR2,
  3  	 p_element   INTEGER,
  4  	 p_separator VARCHAR2 DEFAULT '>')
  5  	RETURN	     VARCHAR2
  6  AS
  7    v_string      VARCHAR2 (32767);
  8  BEGIN
  9    v_string := p_string || p_separator;
 10    FOR i IN 1 .. p_element - 1 LOOP
 11  	 v_string := SUBSTR (v_string,
 12  			     INSTR (v_string, p_separator)
 13  			       + LENGTH (p_separator));
 14    END LOOP;
 15    RETURN SUBSTR (v_string, 1, INSTR (v_string, p_separator) - 1);
 16  END list_element;
 17  /

Function created.

SCOTT@orcl_11g> UPDATE table_name
  2  SET    col2 = list_element (col1, 3),
  3  	    col3 = list_element (col1, 4),
  4  	    col4 = list_element (col1, 5),
  5  	    col5 = list_element (col1, 6),
  6  	    col6 = list_element (col1, 7)
  7  /

5 rows updated.

SCOTT@orcl_11g> SELECT * FROM table_name
  2  /

COL1                                             COL2     COL3     COL4     COL5     COL6
------------------------------------------------ -------- -------- -------- -------- --------
>justice>carlosd>cellswor>jheadley               carlosd  cellswor jheadley
>justice>carlosd>cellswor>ggalster>csyoon        carlosd  cellswor ggalster csyoon
>justice>carlosd>jsauer                          carlosd  jsauer
>justice
>justice>carlosd>amalani>mlewis>bteasdal>valotto carlosd  amalani  mlewis   bteasdal valotto

SCOTT@orcl_11g> 

Previous Topic: Distinct grouping
Next Topic: Group By Case
Goto Forum:
  


Current Time: Mon Dec 05 13:15:41 CST 2016

Total time taken to generate the page: 0.10471 seconds