nsaireddymca avatar image
nsaireddymca asked

how to split the column data if spaces are not there and store it into new table?

hi, i got one requirement i.e we purchased one database , before that we are using one database for application. now in new database the data for column is stored by merging unit+drugname+measure columns. now my requirement is i have to use same table structure which is having previously and used for application .but i have to get data from newly purchased database table and store it into my old table.because the newly purchased database table data is hord-coded. here i am giving one simple example and my required output , kindly have a look on it. **Table-1**: CREATE TABLE MASTERT(CODE INT, DRUGNAME VARCHAR(20), ROUTE VARCHAR(20)) INSERT MASTERT VALUES(2567,'30SESICONMG','SP') INSERT MASTERT VALUES(1456,'30AMERIZOMERISMG','SP') INSERT MASTERT VALUES(246789,'30LOCTON-ZENICMG','SP') INSERT MASTERT VALUES(134,'30TWENTERZARMG','SP') Sample data: Code DRUGNAME ROUTE ----------------------------------------------------------- 2567 30SESICONMG SP 1456 30AMERIZOMERISMG SP 246789 30LOCTON-ZENICMG SP **TABLE2** CREATE TABLE DRUGDESRIPTION(CODE CHAR(3), DESCRIPTION VARCHAR(50)) INSERT DRUGDESRIPTION VALUES('SP','SPRUNPETA') Sample data: CODE DESRIPTION ----------------------- SP SPRUNPETA **TABLE-3**: CREATE TABLE DRUG (CODE INT, DRUGNAME VARCHAR(50), ROUTE VARCHAR(20), UNIT INT, MEASURE VARCHAR(20) ) My expected output is like below CODE DRUGNAME ROUTE UNIT MEASURE ------------------------------------------------------------------ 2567 SESICON SPRUNPETA 30 MG 1456 AMERIZOMERIS SPRUNPETA 30 MG 246789 LOCTON-ZENIC SPRUNPETA 30 MG It means I am getting the data from `DRUGDESRIPTION` and `mastert` tables and store it into `DRUG` table. Note that under the `drugname` column we cannot expect the length of drug name data like in some columns (`SESICON, AMERIZOMERIS, LOCTON-ZENIC`) here column length may also increase. actually i know 30 is the unit and mg is measure and SESICON, AMERIZOMERIS, LOCTON-ZENIC are drugnames. but there is no space in between words then how can i split it and store it. Can anyone help me as early as possible please. Thanks in advance.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers


Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.