Home » SQL & PL/SQL » SQL & PL/SQL » Comma Seprate (11g )
Comma Seprate [message #668853] |
Mon, 19 March 2018 05:46 |
|
glmjoy
Messages: 187 Registered: September 2011 Location: KR
|
Senior Member |
|
|
CREATE TABLE ABC (TOTAL_NAME VARCHAR2(4000));
insert into ABC VALUES('2237,GASTROSCOPY+BANDLIGATION EXC BAND,11232,11232,11232,11232,11,232,1');
insert into ABC VALUES('2238,GASTRIC VARICEAL INJECTION ( GENERAL,19500,19500,19500,19500,19,500,1');
insert into ABC VALUES('2239,GASTROSCOPY+BANDLIGATION EXC BAND,11232,11232,11232,11232,11232,1');
insert into ABC VALUES('2240,GRAFT STUDY,39000,39000,39000,39000,39000,1);
Select TOTAL_NAME FROM ABC
TOTAL_NAME
----------------------------------------------------------------------------------------------------
2237,GASTROSCOPY+BANDLIGATION EXC BAND,11232,11232,11232,11232,11,232,1
2238,GASTRIC VARICEAL INJECTION GENERAL,19500,19500,19500,19500,19,500,1
2239,GASTROSCOPY+BANDLIGATION EXC BAND,11232,11232,11232,11232,11232,1
2240,GRAFT STUDY,39000,39000,39000,39000,39000,1
4 rows selected.
--------------------------------------------------------------------------
CREATE TABLE RATE_TMP
(
PROC_CODE VARCHAR2(6 BYTE) NOT NULL,
PROC_NAME VARCHAR2(4000 BYTE),
GEN NUMBER(7) NOT NULL,
SP NUMBER(7),
PVT NUMBER(7) NOT NULL,
DLX NUMBER(7),
VIP NUMBER(7),
C_ID VARCHAR2(6 BYTE) NOT NULL
)
I WANT TO INSERT ABC COMMA SPERATED DATA into RATE TABLE Like This.
PROC_CODE PROC_NAME GEN SP PVT DLX VIP C_ID
2237 GASTROSCOPY+BANDLIGATION EXC BAND 11232 11232 11232 11232 11232 1
2238 GASTRIC VARICEAL INJECTION GENERAL 19500 19500 19500 19500 19500 1
2239 GASTROSCOPY+BANDLIGATION EXC BAND 11232 11232 11232 11232 11232 1
2240 GRAFT STUDY 39000 39000 39000 39000 39000 1
[mod-edit: code tags added by BB]
[Updated on: Mon, 19 March 2018 14:57] by Moderator Report message to a moderator
|
|
|
|
|
Re: Comma Seprate [message #668864 is a reply to message #668853] |
Mon, 19 March 2018 15:24 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
I had to remove a couple of commas from numbers in your insert statements: 11,232 and 19,500. If those commas are actually in your values, then you will have to find some other way to obtain your data. If the commas used as delimiters are contained in the data between the delimiters, then it will cause it to separate the numbers into two columns and bump the remaining columns one column to the right. You would need to either use some other delimiter that is not contained in the data or use enclosing characters that are not contained in the data.
SCOTT@orcl_12.1.0.2.0> CREATE TABLE ABC (TOTAL_NAME VARCHAR2(4000));
Table created.
SCOTT@orcl_12.1.0.2.0> insert into ABC VALUES('2237,GASTROSCOPY+BANDLIGATION EXC BAND,11232,11232,11232,11232,11232,1');
1 row created.
SCOTT@orcl_12.1.0.2.0> insert into ABC VALUES('2238,GASTRIC VARICEAL INJECTION ( GENERAL,19500,19500,19500,19500,19500,1');
1 row created.
SCOTT@orcl_12.1.0.2.0> insert into ABC VALUES('2239,GASTROSCOPY+BANDLIGATION EXC BAND,11232,11232,11232,11232,11232,1');
1 row created.
SCOTT@orcl_12.1.0.2.0> insert into ABC VALUES('2240,GRAFT STUDY,39000,39000,39000,39000,39000,1');
1 row created.
SCOTT@orcl_12.1.0.2.0> CREATE TABLE RATE_TMP
2 (
3 PROC_CODE VARCHAR2(6 BYTE) NOT NULL,
4 PROC_NAME VARCHAR2(4000 BYTE),
5 GEN NUMBER(7) NOT NULL,
6 SP NUMBER(7),
7 PVT NUMBER(7) NOT NULL,
8 DLX NUMBER(7),
9 VIP NUMBER(7),
10 C_ID VARCHAR2(6 BYTE) NOT NULL
11 )
12 /
Table created.
SCOTT@orcl_12.1.0.2.0> Select TOTAL_NAME FROM ABC
2 /
TOTAL_NAME
---------------------------------------------------------------------------------------------------------
2237,GASTROSCOPY+BANDLIGATION EXC BAND,11232,11232,11232,11232,11232,1
2238,GASTRIC VARICEAL INJECTION ( GENERAL,19500,19500,19500,19500,19500,1
2239,GASTROSCOPY+BANDLIGATION EXC BAND,11232,11232,11232,11232,11232,1
2240,GRAFT STUDY,39000,39000,39000,39000,39000,1
4 rows selected.
SCOTT@orcl_12.1.0.2.0> INSERT INTO rate_tmp
2 SELECT REGEXP_SUBSTR (total_name, '[^,]+', 1, 1),
3 REGEXP_SUBSTR (total_name, '[^,]+', 1, 2),
4 REGEXP_SUBSTR (total_name, '[^,]+', 1, 3),
5 REGEXP_SUBSTR (total_name, '[^,]+', 1, 4),
6 REGEXP_SUBSTR (total_name, '[^,]+', 1, 5),
7 REGEXP_SUBSTR (total_name, '[^,]+', 1, 6),
8 REGEXP_SUBSTR (total_name, '[^,]+', 1, 7),
9 REGEXP_SUBSTR (total_name || ',', '[^,]+', 1, 8)
10 FROM abc
11 /
4 rows created.
SCOTT@orcl_12.1.0.2.0> COLUMN proc_name FORMAT A36
SCOTT@orcl_12.1.0.2.0> SELECT * FROM rate_tmp
2 /
PROC_C PROC_NAME GEN SP PVT DLX VIP C_ID
------ ------------------------------------ ---------- ---------- ---------- ---------- ---------- ------
2237 GASTROSCOPY+BANDLIGATION EXC BAND 11232 11232 11232 11232 11232 1
2238 GASTRIC VARICEAL INJECTION ( GENERAL 19500 19500 19500 19500 19500 1
2239 GASTROSCOPY+BANDLIGATION EXC BAND 11232 11232 11232 11232 11232 1
2240 GRAFT STUDY 39000 39000 39000 39000 39000 1
4 rows selected.
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 18:15:04 CDT 2024
|