Home » SQL & PL/SQL » SQL & PL/SQL » How to convert data values (merged)
| How to convert data values (merged) [message #296706] |
Mon, 28 January 2008 16:30  |
syang
Messages: 30
Registered: February 2007
|
Member |
|
|
Dear All,
We are looking for ideas as to how to impelment the following scenario:
1. User table: Here is the table definition for this user table:
user_id char(7) (primary key)
last_name varchar2(20)
first_name varchar2(20)
nsc_number varchar2(1000)
2. Sample data for user table (notice the nsc data is comma delimited):
user_id last_name first_name nsc_number
------- --------- ---------- ------------------------------
7777777 xxx yyy 1234567890,2345678900,3456789000
3. The business process requires us to decode the data value (comma delimited) on the nsc_number column in the user table and pupoulate nsc data into another table called nsc. Here is the nsc table definition:
user_id char(7)
nsc_number number(10)
4. The result of the nsc table for the user_id (7777777) should look like this:
user_id nsc_number
------------ -------------------
7777777 1234567890
7777777 2345678900
7777777 3456789000
5. Any ideas how we can do this?
Thanks in advance!
|
|
|
|
| How to convert data values [message #296715 is a reply to message #296706] |
Mon, 28 January 2008 18:33  |
syang
Messages: 30
Registered: February 2007
|
Member |
|
|
Hi there,
Does anyone have any feedback on the following?
The goal of this exercise is to convert a group of data values (comma delimited) on the following column (nsc_number) in table A and populate the data values into table B?
1. Table A
user_id nsc_number
------------ ------------------------------------------------
7777777 1234567890,2345678900,3456789000,etc
2. Table B: After the conversion, the data values (comma delimited) in Table A will be decoded into Table B and the result should look like this in Table B:
user_id nsc_number
------------ ----------
7777777 1234567890
7777777 2345678900
7777777 3456789000
....
Question
Is there an oracle function we can use in order to achieve the goal? We are running Oracle 10g2R.
Thanks in advance!
|
|
|
|
| Re: How to convert data values [message #296729 is a reply to message #296715] |
Mon, 28 January 2008 19:46 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
This isn't a fully worked solution, but it might give you some ideas:
1. Create a NESTED TABLE type
CREATE TYPE vc2table AS TABLE OF VARCHAR2(4000);
2. Create a FUNCTION that converts a CSV string into a nested table.
CREATE OR REPLACE FUNCTION csv2table (
csv IN varchar2(4000)
) RETURN vc2table IS
BEGIN
<you do the work here>
END;
3. Use SQL to cast the string to a nested table.
SELECT user_id, csv2table(nsc_number)
FROM table_a
4. Unnest the nested table to give a row for each CSV element
SELECT a.user_id, b.column_value
FROM table_a a
, table(csv2table(a.nsc_number)) b
see example here
Hope that helps
Ross Leishman
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to convert data values (merged) [message #297050 is a reply to message #296706] |
Wed, 30 January 2008 00:33 |
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
|
Senior Member |
|
|
SQL> select * from test;
ID DET
---------- --------------------------------------------------
7777 12345,34567,678995,32678567,123,3443
declare
userid number(4) :=7777;
det1 varchar2(50);
n number(3) :=1;
begin
select det into det1 from test where id=userid;
while (instr(det1,',',1,n)>0)
loop
if (instr(det1,',',1,1)=instr(det1,',',1,n))
then
insert into temp
values(userid,substr(det1,1,instr(det1,',',1,1)-1));
else
insert into temp
values(userid,substr(det1,instr(det1,',',1,n-1)+1,instr(det1,',',1,n)-instr(det1,',',1,n-1)-1));
end if;
n:=n+1;
end loop;
insert into temp
values(userid,substr(det1,instr(det1,',',1,n-1)+1));
commit;
end;
/
SQL> select * from temp;
ID DET
---------- --------------------------------------------------
7777 12345
7777 34567
7777 678995
7777 32678567
7777 123
7777 3443
6 rows selected.
update procedure if you need to fetch multiple user from table itself.
[Updated on: Wed, 30 January 2008 00:54] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: How to convert data values (merged) [message #297740 is a reply to message #297446] |
Sun, 03 February 2008 04:01 |
Volder
Messages: 38
Registered: April 2007
Location: Russia
|
Member |
|
|
SQL> with t as (select 7777777 num, '1234567890,2345678900,3456789000' str from dual)
2 select num, regexp_substr(str, '[^,]+', 1, level) sub from t
3 connect by level <= nvl(length(regexp_replace(str, '[^,]')),0) + 1
4 /
NUM SUB
---------- --------------------------------
7777777 1234567890
7777777 2345678900
7777777 3456789000
SQL>
|
|
|
|
Goto Forum:
Current Time: Thu Jul 10 18:52:21 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
