Extracting portion of string using delimiters
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=19603
Printed Date: 25 Apr 2024 at 9:29am
Topic: Extracting portion of string using delimiters
Posted By: siguy
Subject: Extracting portion of string using delimiters
Date Posted: 01 Jun 2013 at 11:10am
Hello All,
A bit new to this, but hope my topic/query makes sense.
I was hoping to get some help on extracting and displaying a portion of text in a particular field using delimiters.
The field in question may contain several sentences, but I have been specific in the data stored in this field i.e. each sentence should start with a code like "01A-","01B-" etc. These are all referenced/linked to data in another field (which I've worked out). I want to be able to ONLY extract text that follows a particular code and end at a specified delimiter i.e. "¬".
An example of data in this field {F_TASK.TASK_NOTES} could be;
01A-Walls dirty¬
02D-Lights missing¬
20E-Grills need cleaning¬
At the moment, I use an 'if' statement to tell Crystal that if the first field contains specified text, to link to anything within the {F_TASK.TASK.NOTES} field. However, i only need it to link to its counterpart corresponding to its code and omit everything else.
A swift reply would be very much appreciated, as I am desperate to get this part of the report done by the end of the week, which my director will be expecting to see.
Thanks in advance.
|
Replies:
Posted By: kevlray
Date Posted: 03 Jun 2013 at 6:57am
It is not real clear what results you are expecting from the data you have provided. It appears you want to show the remaining text after the code (i.e., Walls Dirty). There is a way to extract that part of the string pretty easy. If all the data shown is in one record. It is still possible to extract the data (a lot more work), it probably will require using an Array. It not clear what you need to do with the extracted string(s).
|
Posted By: siguy
Date Posted: 04 Jun 2013 at 12:22am
Thanks for the post kevlray.
Apologies for the ambiguity.
The end result I basically want, for example, is if a value in my first field {QUESTIONS} is ‘01A’, then display only ‘Walls dirty’ from the other field {NOTES}, rather than the whole content of that field.
i.e.
QUESTION | NOTES
01A Walls dirty
Rather than;
QUESTION | NOTES
01A 01A-Walls dirty¬
02D-Lights missing¬
20E-Grills need cleaning¬
Apologies if this isn't as clear as it could be, but please let me know if there is any other information i can give that can aid the process.
Thanks
|
Posted By: kevlray
Date Posted: 04 Jun 2013 at 7:24am
If the code is always the same length. Try this (two different formulas)
left({F_TASK.TASK.NOTES},3)
mid({F_TASK.TASK.NOTES},5,length({F_TASK.TASK.NOTES})-1)
|
Posted By: siguy
Date Posted: 05 Jun 2013 at 1:21am
|
Thanks Kev,
Unfortunately the first formula only gives me the code i.e. '01A' and the second formula gives me everything to the right of that.
The second formula is almost what I need, only i need it to cut everything else off after the '¬' symbol or a carriage return. Its a little difficult to define the length of each sentence as the sentence lengths are variable, which is why i wanted to know if there was a formula to identify a stop position i.e. '¬' or carriage return.
so firstly, my formula would need to recognise a start position e.g. '01A', and then display the text after it up to the end position of that sentence i.e. '¬' or a carriage return. This will omit the rest of the data/text that follows it, hopefully looking like this;
Walls Dirty
rather then;
01A-Walls dirty¬
02D-Lights missing¬
20E-Grills need cleaning¬
Thanks
|
Posted By: kevlray
Date Posted: 05 Jun 2013 at 6:45am
I thought you needed the code so that is why I included that.
The second formula may need some tweaking. I made the assumption that the 01A (or other code is part of the string), thus starting the mid formula at position 5. It appears I did the math wrong for the remaining part of the string. I believe the formula should be mid({F_TASK.TASK.NOTES},5,length({F_TASK.TASK.NOTES})-6. This would take the length of the string minus the first five characters plus one more to get rid of the delimiter.
|
Posted By: siguy
Date Posted: 05 Jun 2013 at 9:38am
|
I haven't implemented this yet, but would this formula consider sentences of varied lengths? Is there not a way to identify a start position and an end position and extract/display anything in between?
|
Posted By: kevlray
Date Posted: 06 Jun 2013 at 8:38am
|
if the code is a different length, yes. You could use instr again for the start of the mid string. FYI the previous formula was missing a ending paren. mid({F_TASK.TASK.NOTES},instr({F_TASK.TASK.NOTES},"-"),length({F_TASK.TASK.NOTES})-6).
|
Posted By: siguy
Date Posted: 08 Jun 2013 at 10:16am
|
Thanks Kev,
Unfortuantely this isnt what i need.
It does identify what the start position is, but not the end position. It seems this formula just chops the last six characters from the end of the string i.e:
01A 01A-Walls dirty¬
02D-Lights missing¬
20E-Grills need cle
I need to have the rest of the sentence truncated from the '¬' or a carriage return.
|
Posted By: kevlray
Date Posted: 10 Jun 2013 at 5:15am
Apparently I am confused on how the original data looks like. Is '01A-Walls Dirty' one field or multiple fields?
Thanks.
|
Posted By: siguy
Date Posted: 10 Jun 2013 at 8:23am
|
this is all within one field, but there are multiple sentences within that one field, which i am trying to link to values in another field.
So if the first table shows 01A, i want to extract and display everything that is in the second table after the words '01A-' and stop at the end of sentence (possibly of varying lengths.
Similarly if the value in the first table is 02B, i want it to take the second sentence from the second field that begins with '02B-', also ending at the end of the sentence.
Thanks.
|
Posted By: kevlray
Date Posted: 10 Jun 2013 at 9:00am
|
The multiple sentences in one field is what I missed. That complicates the formula. I am not sure when I will have time to look at this. Sorry. :-(
|
Posted By: siguy
Date Posted: 10 Jun 2013 at 11:06am
Kev you've been brilliant in spending your efforts for me, so no need to apologise and thanks for the help so far.
What I tried to do as a work around is use multiple 'if' statements with your formula;
if {F_TASK.TASK_QUESTIONS} LIKE "*01A*" then
mid({F_TASK.TASK.NOTES},instr({F_TASK.TASK.NOTES},"01A-"),length({F_TASK.TASK.NOTES})) else
if {F_TASK.TASK_QUESTIONS} LIKE "*01B*" then
mid({F_TASK.TASK.NOTES},instr({F_TASK.TASK.NOTES},"01B-"),length({F_TASK.TASK.NOTES})) else..... and so on so forth.
Problem with this is that I can only use this as long as i don't have the 'can grow' option ticked, which will cut subsequent sentences off, and only if they are short single line sentences. Also, the formula only works if there are sentences in {F_TASK.TASK.NOTES} that contain the codes stated in the formula, if there isn't, i get a validation error along the lines of 'this is not an integer' or something similar. My guess is that I haven't defined what the formula should do if these items are not present - I have no idea how to close multiple 'if' statements and tried 'end if', but this didn't work.
Anyway thanks all the same!
|
|