INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Parse out columns of text

Parse out columns of text

(OP)
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.

RE: Parse out columns of text

Quote (rmhealth)

. . . 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.
3eyes

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

RE: Parse out columns of text

If you have "a field that contains single value", then there is nothing to parse.
Unless you actually have something like:

MyField
012*34*4*7


or

MyField
0 1 2 * 3 4 * 4 * 7


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

That does not look very ‘normalized’... sad

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.

RE: Parse out columns of text

(OP)
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

RE: Parse out columns of text


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?
noevil

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

RE: Parse out columns of text

(OP)
"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 ...

RE: Parse out columns of text


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

CODE

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 
[thumbsup2

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

RE: Parse out columns of text

Let's say this is your tblSecurity:

ID  SECURITY
208 22020002200**2****2*******************************
210 000*000**00***************************************
211 20000000200*******0*******************************
213 20000000200*******0*******************************
 

You can do something like this since SECURITY field will always have 50 elements:

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


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.

RE: Parse out columns of text

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

CODE --> SQL

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; 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close