Home » SQL & PL/SQL » SQL & PL/SQL » Comma Seprate (11g )
Comma Seprate [message #668853] Mon, 19 March 2018 05:46 Go to next message
glmjoy
Messages: 166
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 #668857 is a reply to message #668853] Mon, 19 March 2018 07:04 Go to previous messageGo to next message
Michel Cadot
Messages: 66808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What about formatting your post as repeated to you many times?
And give feedback?

Re: Comma Seprate [message #668860 is a reply to message #668857] Mon, 19 March 2018 10:09 Go to previous messageGo to next message
glmjoy
Messages: 166
Registered: September 2011
Location: KR
Senior Member
Oh sorry for that
Re: Comma Seprate [message #668864 is a reply to message #668853] Mon, 19 March 2018 15:24 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
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.
Re: Comma Seprate [message #668892 is a reply to message #668864] Tue, 20 March 2018 18:19 Go to previous message
glmjoy
Messages: 166
Registered: September 2011
Location: KR
Senior Member
Thanks a lot Barbara Boehmer it works.
Previous Topic: return records based on records
Next Topic: Transaction 2 happen within 60 days of Transaction 1
Goto Forum:
  


Current Time: Wed Jan 29 01:59:35 CST 2020