CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11gplsqldeveloper
Monica
Monica

Reputation: 169

Data parsing from one column to multiple columns

I have a text file with data as follows:

A 123 JoeBSD1639973e7378790
A 344 TomGDF7393694e0902087
B 344 ZenGDF9838987e0849399

I have used sqlldr to bulk insert into a temp table and all the data is in one column since I do not have any field delimiter here.

Now I want to segregate this data into columns as follows:

Section    Book1   Name   Book2   RollNo   Book3   RegNo
----------------------------------------------------------
A          123     Joe    BSD     1639973  e       7378790
A          344     Tom    GDF     7393694  e       0902087
B          344     Zen    GDF     9838987  e       0849399

The way I can identify the fields is using the char lengths like Section is 2 length, Book1 is 4, Name is 3, Book2 is 3, RollNo is 7, Book3 is 1 and RegNo is 7.

Can someone help me with the query to do this transformation?

Upvotes: 0

Views: 1271

Answers (5)

Art
Art

Reputation: 5792

This may also help. I have similar problem and asked for help in Oracle-Base forums. This query was written by Tim - moderator of Oracle-Base.com:

SELECT REGEXP_SUBSTR(str, '[A-Z][a-z]+', 1, 1) col1,
       REGEXP_SUBSTR(str, '[A-Z]+', 1, 2) col2,
       REGEXP_SUBSTR(str, '[0-9]+', 1, 1) col3,
       REGEXP_SUBSTR(str, '[a-z]+', 1, 2) col4,
       REGEXP_SUBSTR(str, '[0-9]+', 1, 2) col5
  FROM
  (
   SELECT 'JoeBSD1639973e7378790' str FROM dual
  )
  /

COL1  COL2  COL3     COL4  COL5
-----------------------------------
Joe   BSD   1639973   e    7378790

Upvotes: 0

Aspirant
Aspirant

Reputation: 2278

We can also use UTL files to extract data from flat files and UTL files will extract data as string itself , so as you konw the lengths you can use SUBSTR to extract the correct column values and then insert into temp table

declare 
     f1 UTL_FILE.FILE_TYPE;
     V_str varchar(2000);
begin
     f1 := UTL_FILE.FOPEN('Directory','File_NAME.txt','W');
     loop
         UTL_FILE.GETLINE(F1,v_str);
         v1:=substr(v_str,1,2);
         v2:=substr(v_str,3,4);
         v3:=substr(v_str,7,3);
         v4:=substr(v_str,10,3);
         v5:=substr(v_str,13,7);
         v6:=substr(v_str,20,1);
         v7:=substr(v_str,21,7);
         insert into temp values(v1,v2,v3,v4,v5,v6,v7);
    end loop;
 exception
    when no_data_found then
       dbms_output.put_line('end reached');
end;

Upvotes: 1

DazzaL
DazzaL

Reputation: 21993

you can use substr

eg:

with data as (select 'A 123 JoeBSD1639973e7378790' str from dual union all
select 'A 344 TomGDF7393694e0902087' from dual union all
select 'B 344 ZenGDF9838987e0849399' from dual)
select str,
       substr(str, 1, 1) section,
       substr(str, 3, 3) book1,
       substr(str, 7, 3) name,
       substr(str, 10, 3) book2,
       substr(str, 13, 7) rollno,
       substr(str, 20, 1) book3,
       substr(str, 21, 7) regno
  from data;

Upvotes: 1

xQbert
xQbert

Reputation: 35343

Here's an example:

Select trim(substr('B 344 ZenGDF9838987e0849399',1,2)) as Section, 
       trim(substr('B 344 ZenGDF9838987e0849399',3,4)) as Book1,
       trim(substr('B 344 ZenGDF9838987e0849399',7,3)) as Name,
       trim(substr('B 344 ZenGDF9838987e0849399',10,3)) as Book2,
       trim(substr('B 344 ZenGDF9838987e0849399',13,7)) as RollNo,
       trim(substr('B 344 ZenGDF9838987e0849399',20,1)) as Book2,
       trim(substr('B 344 ZenGDF9838987e0849399',21,7)) as RegionNo
from dual

I used trim to eliminate spaces; but maybe they are relevant?

Upvotes: 0

David Aldridge
David Aldridge

Reputation: 52376

The SubStr() function is what you need: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions162.htm

You might like to consider using an external table instead of SQL Loader inserting to a temporary table, as you can more easily apply the SQL functions to the file data and split out the columns during the reading of the file.

Upvotes: 1

Related Questions