By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,825 Members | 1,697 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,825 IT Pros & Developers. It's quick & easy.

split a contents of a field of each record and store in another table

P: 9
Hai everybody...!
I have a problem here .. Hope I can get help by answers..

I have a table A
ID DATA
1 aa;bb
2 aa;bb;cc
3 aa;cc
4 bb;cc

I wish to get a table B like

ID A B C
1 aa bb null
2 aa bb cc
3 aa null cc
4 null bb cc

I would like get table B a result by using DB2 UDF,,
wish have details of create function & how to call it ..
I am new to DB2.. waiting for Help..!

Thanks in advance...
Mar 19 '13 #1
Share this Question
Share on Google+
12 Replies


10K+
P: 13,264
You'd have to write your own string split function. Luckily someone has already done that and explained the process here:http://www.ibm.com/developerworks/da...03stolze1.html
Mar 19 '13 #2

P: 9
Thanks for your response..
I have tried that one earlier.. it throws [Error] - &;WHERE ordinal. SQLCODE=-7, SQLSTATE=42601, DRIVER=3.53.71

I am using DB2 Aqua Data Studio 8.0.8

so waiting for further clarification..
It would be very helpful ..
Thanks once again in advance..
Mar 19 '13 #3

10K+
P: 13,264
What code did you execute when you go that error?
Mar 19 '13 #4

P: 9
Here is the code I used...

Expand|Select|Wrap|Line Numbers
  1. 1)CREATE or REPLACE FUNCTION elemIdx (string CLOB(64K) ) 
  2. 2)RETURNS TABLE ( ordinal INTEGER, index INTEGER ) 
  3. 3)LANGUAGE SQL 
  4. 4)DETERMINISTIC 
  5. 5)NO EXTERNAL ACTION 
  6. 6)CONTAINS SQL 
  7. 7)RETURN WITH t(ordinal, index) AS 
  8. 8) ( VALUES ( 0, 0 ) 
  9. 9)  UNION ALL 
  10. 10) SELECT ordinal+1, COALESCE(NULLIF(LOCATE(';', 
  11. 11) string,index+1),0),LENGTH(string)+1) FROM t           
  12. 12) WHERE  ordinal < 10000 AND LOCATE(';', string, 
  13. 13)     index+1) <> 0 ) 
  14. 14)     SELECT ordinal, index  FROM   t 
  15. 15)     UNION ALL       
  16. 16)     SELECT MAX(ordinal)+1, LENGTH(string)+1 FROM   t 
In the above code I am not clear about the following...

[ CLOB(64K) ]
[ <10000 AND ]
[ <> ]

Thanks in advance..
Mar 19 '13 #5

10K+
P: 13,264
Don't just copy and paste code without looking at what you are executing. That is quite a dangerous thing to do.

1.) Don't execute it with those line numbers
2.) Read the tutorial to understand what the SQL is doing.
Mar 19 '13 #6

P: 9
Thanks..
I do executed without line numbers ..
just for understanding i added line number manually for posting reply...
as i mentioned early
[ CLOB(64K) ]
[ <10000 AND ]
[ <> ]

are not mentioned clearly in tutorial...
Thanks again..
Mar 19 '13 #7

10K+
P: 13,264
Most likely some of the characters are supposed to be less than (<) instead of &lt; and their code formatter didn't display them right so replace &lt; with < and &gt; with >
Mar 19 '13 #8

P: 9
yes you are right...!

http://www.ibm.com/developerworks/da...03stolze1.html

helps to get the seperate Stings from a group of String as input ...

i.e.s from 'aa;bb;cc' as input gives 'aa' 'bb' 'cc' in three records as output...

but still i am not getting how to get ...

table B
ID A B C
1 aa bb null
2 aa bb cc
3 aa null cc
4 null bb cc

as output from the input

table A
ID DATA
1 aa;bb
2 aa;bb;cc
3 aa;cc
4 bb;cc

i.e., how to pass each DATA in all records of table A as String (by looping) as input
& how to insert the result into the fields A B C of table B respectively..

Thanks Again in Advance...
Mar 20 '13 #9

10K+
P: 13,264
insert from a select (or from a group of selects)
Mar 20 '13 #10

P: 9
Thanks.. but still not getting how to insert with group of select...
I have the following...
[code - function returns a table]
Expand|Select|Wrap|Line Numbers
  1. CREATE or REPLACE FUNCTION elemIdx ( string VARCHAR(225) ) 
  2.    RETURNS TABLE ( ordinal INTEGER, index INTEGER ) 
  3.    LANGUAGE SQL 
  4.    DETERMINISTIC 
  5.    NO EXTERNAL ACTION 
  6.    CONTAINS SQL 
  7.    RETURN 
  8.       WITH t(ordinal, index) AS 
  9.          ( VALUES ( 0, 0 ) 
  10.            UNION ALL 
  11.            SELECT ordinal+1, COALESCE(NULLIF(LOCATE(';', string, index+1), 0),LENGTH(string)+1) FROM   t 
  12.                   WHERE  ordinal<1000 AND  LOCATE(';', string, index+1) <> 0 ) 
  13.       SELECT ordinal, index FROM   t 
  14.       UNION ALL 
  15.       SELECT MAX(ordinal)+1, LENGTH(string)+1 FROM t 
  16.  
  17.  
[code - query ]
Expand|Select|Wrap|Line Numbers
  1. SELECT * 
  2. FROM TABLE (elemIdx('abc; def; ghi; 123') ) AS t ORDER BY 1
  3.  
[result of above query - a table]
Expand|Select|Wrap|Line Numbers
  1.   ordinal  index
  2.     0       0
  3.     1       4
  4.     2       9
  5.     3       14
  6.     4       19
[result]

[code-function returns a table]
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE FUNCTION elements ( string VARCHAR(225) ) 
  2.    RETURNS TABLE ( elements VARCHAR(225) ) 
  3.    LANGUAGE SQL 
  4.    DETERMINISTIC 
  5.    NO EXTERNAL ACTION 
  6.    CONTAINS SQL 
  7.    RETURN 
  8.       WITH t(ordinal, index) AS 
  9.          ( SELECT ordinal, index FROM   TABLE ( elemIdx(string) ) AS x ) 
  10.       SELECT SUBSTR(string, t1.index+1, t2.index - t1.index - 1) 
  11.             FROM   t AS t1 JOIN t AS t2 ON ( t2.ordinal = t1.ordinal+1 ) 
  12.  
  13.  
[code- query that returns a table again]
Expand|Select|Wrap|Line Numbers
  1. SELECT VARCHAR(elem, 100) 
  2. FROM   TABLE ( elements('abc; def; ghi; 123') ) AS t(elem)
  3.  
  4.  
[result of above query - a table]
Expand|Select|Wrap|Line Numbers
  1.   1
  2.  abc
  3.  def
  4.  ghi
  5.  123
  6.  
[result]

i donot have any clear idea about
how to proceed further....

Help me how to proceed ... Thanks in Advance...
Mar 20 '13 #11

10K+
P: 13,264
insert into your new table values(colA, colB)
values
(select from the table with the concatenated values and split the values and get the first split record, select from the table with the concatenated values and split the values and get the second split record)
Mar 20 '13 #12

P: 9
Thanks...
I am not getting any progress...
as I am getting close to deadline,could you help me with queries using above functions..

source table is
Table_name as source
ID DATA
1 aa;bb
2 aa;bb;cc
3 aa;cc
4 bb;cc

and destination table is

Table_name as destination (ID is auto generated)
ID A B C
- - - -

expected result is
Table_name: destination
ID A B C
1 aa bb null
2 aa bb cc
3 aa null cc
4 null bb cc

Thanks in Advance..
Mar 20 '13 #13

Post your reply

Sign in to post your reply or Sign up for a free account.