Parse out columns of text 1

[rmhealth]

Hi, All. I have a field that contains single value data (in the form of 0. or 1. or2, or *). The field is 50-wide. Any way I can parse it out into separate columns, one value per column number 1 to 50?
Thanks.

 

[LKBrwnDBA]

. . . Any way I can parse it out into separate columns, one value per column number 1 to 50?

Yes, depends on the rules you set to delimit the elements.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Andrzejek]

If you have "a field that contains single value", then there is nothing to parse.
Unless you actually have something like:
[tt]
MyField
012*34*4*7[/tt]

or
[tt]
MyField
0 1 2 * 3 4 * 4 * 7[/tt]

And what you are saying you want to have:
[pre]
F1 F2 F3 F4 F5 F6 ... F50
0 1 2 * 3 4 [/pre]

That does not look very ‘normalized’... :-(

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[rmhealth]

Hi, Andy. Yup, that's my goal
F1 F2 F3 F4 F5 F6 ... F50
0 1 2 * 3 4

and, no, it's not normalized. It's a remnant from the original garage-born DB. Now the new publisher has carted it to Oracle. I can't see what routine they use to parse it out. But each column represents a security group. If you are in group one and the web page as a 0 in colu7mn one, then you have no access, 1= full and 2 = read-only and * = Group Default.

I now have a superintendent who wants a report on who has access to what pages. I can do this in Excel, but it would be more easily repeatable if I could do it in SQL

 

[LKBrwnDBA]

Well if you do not provide a sample of the source data and the expected results, how can you presume any solution can be delivered?



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[rmhealth]

ID" "PATH" "SECURITY"
208 "importexport/exportusingtemplate/home.html" "22020002200**2****2*******************************"
210 "family/bulkchange.html" "000*000**00***************************************"
211 "importexport/quickimport/quickimport1.html" "20000000200*******0*******************************"
213 "importexport/importtemplates/home.html" "20000000200*******0*******************************"

Expected result
ID G1 G2 G3 G4 G5 G6 ... G50
208 2 2 0 2 0 0 ... *

From here I can add column titles. Group1 is secretaries, Group2 is technicians, Group3 Counselors ...

 

[LKBrwnDBA]

Ok, suppose the name of your source file is "infile.txt" try this:

awk -F' ' 'BEGIN{printf"ID\t";for(i=1;i<=50;++i){printf"%s\t","G"i;}print ""}
{ printf"%s\t",$1;for(i=3;i<=53;++i){printf"%s\t",substr($3,(i+1),1);}print ""}
' infile.txt

[


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Andrzejek]

Let's say this is your tblSecurity:
[pre]

ID SECURITY
208 22020002200**2****2*******************************
210 000*000**00***************************************
211 20000000200*******0*******************************
213 20000000200*******0*******************************
[/pre]

You can do something like this since SECURITY field will always have 50 elements:
[tt]
Insert INTO YourNewTable
Select ID,
SUBSTR(SECURITY, 1, 1),
SUBSTR(SECURITY, 2, 1),
SUBSTR(SECURITY, 3, 1),
SUBSTR(SECURITY, 4, 1),
SUBSTR(SECURITY, 5, 1),
SUBSTR(SECURITY, 6, 1),
...
SUBSTR(SECURITY, 49, 1)
From tblSecuroty
[/tt]

I am sure there are better, more 'elegant' ways to do it.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Dagon]

If you desperately wanted to use the Oracle 11 pivot function, you could do something like this:

create table testdenorm (id number, path varchar2(100 char), security varchar2(50 char));

insert into testdenorm values 
( 208,    'importexport/exportusingtemplate/home.html','22020002200**2****2*******************************');

insert into testdenorm values 
( 210,    'importexport/quickimport/quickimport1.html','000*000**00***************************************');

insert into testdenorm values 
( 213,    'importexport/importtemplates/home.html', '20000000200*******0*******************************');

select * from 
(
select id, path, security, r.pos, substr(t.security, r.pos,1) posval 
from testdenorm t,
     (select level pos from dual
      connect by level <= 50) r
)
pivot
(
   min(posval)
   for pos in (1 "Secretaries", 2 "Technicians", 3 "Counselors", 4 "Engineers")
)
order by id;