Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search and Extract from a String 3

Status
Not open for further replies.

dixie617

Programmer
Jun 8, 2005
62
US
I have a very large memo field in a oracle database that I need to search for the "Imported By:" and extract the name from the field. I have tried numerous formulas but nothing seems to work right. Here is a sample of the field:

{\rtf1\ansi\ftnbj{\fonttbl{\f0 \fmodern Courier New;}{\f1 \fswiss Arial;}}{\colortbl ;\red255\green255\blue255 ;\red0\green0\blue0 ;}{\stylesheet{\f0\fs20\cf0\cb1 Normal;}{\cs1\additive\cf0\cb1 Default Paragraph Font;}}\margl1440\margr1440\margt540\margb1440\headery540\footery720\formshade\sectd\marglsxn1440\margrsxn1440\margtsxn540\margbsxn1440\headery540\footery720\sbkpage\pgncont\plain\plain\fs20\pard\plain\fs20\cf0\f1\sscharaux10001\b\protect WCC: 5 Yr.Old\b0\par\sscharaux0\protect0\par\sscharaux10002\protect See attached scanned report\par\sscharaux0\protect0\par\par\sscharaux10003\i\protect Imported By: Kerry Parascando, MA 03/26/2009 10:40:59\i0\par\f0\cf2\sscharaux0\protect0\par\sscharaux10004\protect _____________________________________________________________________\par\sscharaux0\protect0\par\sscharaux10005\protect External Attachment:\par\sscharaux0\protect0\par\sscharaux10006\protect Type:\tab Image \par Comment:\tab External Document\f1\cf0\sscharaux0\protect0\par}

Any help would be greatly appreciated. Thanks Christine
 
Provided name is always preceded by 'Imported By: ' and the name is followed by ',' then this should work

split(split('{yourfield}', 'Imported By: ')[2],',')[1]

Also your field is likely to be a memo so you will need to be working with at least CR10 and above. Prior versions would not allow memofields to be used in formula.

Ian
 
I am using CRXI, I tried your formula but got the following error:
"A subscript must be between 1 and the size fo the array."
Thanks
 
I got it to work thank you so much. Another question, how do I remove data from after the name. Some I got just the name some I got the following (maybe because no coma after the name?):
Beatriz Guerra 03/24/2009 16:04:59\i0\par\f0\cf2\sscharaux0\protect0\par\sscharaux10004\protect _____________________________________________________________________\par\sscharaux0\protect0\par\sscharaux10005\protect External Attachment:\par\sscharaux0\protect0\par\sscharaux10006\protect Type:\tab Image \par Comment:\tab External Document\f1\cf0\sscharaux0\protect0\par}
 
You can use the left() function, but how will Crystal logically know where the name ends? Will there always be a date right after that? Will the date always have a 2 digit month?

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:
 
If there is no comma will the name always be
firstname lastname

each part followed by a space?

could try

split(split('{yourfield}', 'Imported By: ')[2],'')[1]&" "&
split(split('{yourfield}', 'Imported By: ')[2],'')[2]

Note i have cahnged second split to search for space.

Ian
 
Should have been

split(split('{yourfield}', 'Imported By: ')[2],' ')[1]&" "&
split(split('{yourfield}', 'Imported By: ')[2],' ')[2]

Ian
 
Ian, thank you so much, worked great. What the name is, is the user that scanned in the document, the last name field in some cases have a "," behind it followed by there license type and others do not but all will be followed by the datetime in the same format. Now that I have it working would it be possible to use it as a group by?

Thanks again for all the information
 
Its not a print time formula so should be OK to group by it.

Only issue may be speed, if you have a large record set this formula may slow it down a bit.

Ian
 
When I tried to add the formula as a group by I got this error again:

"A subscript must be between 1 and the size fo the array.
 
It must be finding a record which does not meet the split criteria.

Go back to a simple listing, sort on the formula and scroll through report page by page. YOu should be able to find the offending record and then change split criteria as required.

You may be able to find a work around by using unbound (see help). But I think that can only be used with arrays and variables which will mean that it can not be used for grouping.

What database are you working with, your dba may be albe to build a view or stored procedure and do all this work there rather than in the report.

Ian
 
The database is oracle 10G, and read only, is a copy of our Centricity Production database. Can't create any views or stored procedures, I wish I could. Is it possible to do this in a universe? We have the Edge PRO system from BO, I just have not had the training on how to use the universe builder yet.
 
Found the offending records, some still have a comma behind the last name. What is the best way to modify this to take that out also?

split(split({DOCDATA.DATA}, 'Imported By: ')[2],' ')[1]&" "&
split(split({DOCDATA.DATA}, 'Imported By: ')[2],' ')[2]
 
That wouldn't create the problem you are having. Try this:

stringvar array x;
stringvar array y;
if instr({DOCDATA.DATA},"Imported By:") > 0 then (
x := split({DOCDATA.DATA}, 'Imported By:');
y := split(trim(x[2]),' ')
);
y[1]&" "&
(
if ubound(y) > 1 then
replace(y[2],",","")
)

-LB
 
LB,
Tried your formula but it is giving the error "the work 'else' is missing
 
Please post the formula exactly as you implemented it.

-LB
 
I copied the exact of what you wrote and pasted, thanks and here is what I have in the formula:

stringvar array x;
stringvar array y;
if instr({DOCDATA.DATA},"Imported By:") > 0 then (
x := split({DOCDATA.DATA}, 'Imported By:');
y := split(trim(x[2]),' ')
);
y[1]&" "&
(
if ubound(y) > 1 then
replace(y[2],",","")
)
 
Where is your cursor when it returns the error?

-LB
 
Change it to:

stringvar array x;
stringvar array y;
if instr({DOCDATA.DATA},"Imported By:") > 0 then (
x := split({DOCDATA.DATA}, 'Imported By:');
y := split(trim(x[2]),' ')
) else
(
x := "";
y := ""
);
y[1]&" "&
(
if ubound(y) > 1 then
replace(y[2],",","")
)

-LB
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top