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 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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: Looking for Ideas on data manipulation [message #296735 is a reply to message #296706] Mon, 28 January 2008 22:19 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

There are lots of methods to do the same.

Some of them are,

1. Varying in list

2. Pipeline Method / Table Function

Thumbs Up
Rajuvan.
Re: How to convert data values [message #296764 is a reply to message #296715] Tue, 29 January 2008 00:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This may be useful: DBMS_UTILITY.COMMA_TO_TABLE

Regards
Michel
Re: Looking for Ideas on data manipulation [message #296770 is a reply to message #296706] Tue, 29 January 2008 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
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:

I hope this is a ONE shot and you will definitively move to the final design with 2 tables and remove this awful nsc_number string.

Regards
Michel
Re: How to convert data values (merged) [message #296975 is a reply to message #296706] Tue, 29 January 2008 15:38 Go to previous messageGo to next message
shivaram9
Messages: 35
Registered: August 2006
Member
Is there a limit as to how many comma delimted values you would have "1234567890,2345678900,3456789000".

is it fixed or varibale
like in the above case there are three, will this vary.

Thanks,
Shiva
Re: How to convert data values (merged) [message #296978 is a reply to message #296975] Tue, 29 January 2008 15:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think ",etc" answers your question.

Regards
Michel
Re: How to convert data values (merged) [message #297050 is a reply to message #296706] Wed, 30 January 2008 00:33 Go to previous messageGo to next message
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 #297068 is a reply to message #297050] Wed, 30 January 2008 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What a complicated way to do it when there are so many fine ways that are already been posted.

Regards
Michel
Re: How to convert data values (merged) [message #297446 is a reply to message #297068] Thu, 31 January 2008 11:59 Go to previous messageGo to next message
syang
Messages: 30
Registered: February 2007
Member
Hi Michel,

Which way is the fine way to do this?

Thanks!
Re: How to convert data values (merged) [message #297447 is a reply to message #297446] Thu, 31 January 2008 12:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
See Rajuvan's and my posts.

Regards
Michel
Re: How to convert data values (merged) [message #297740 is a reply to message #297446] Sun, 03 February 2008 04:01 Go to previous message
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> 
Previous Topic: UTL_FILE error
Next Topic: Query to get the last 100 rows in a table
Goto Forum:
  


Current Time: Wed Dec 07 16:16:38 CST 2016

Total time taken to generate the page: 0.12856 seconds