Truncating based on character rather than length
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=13013
Printed Date: 04 May 2024 at 3:48am
Topic: Truncating based on character rather than length
Posted By: Erinmichelle
Subject: Truncating based on character rather than length
Date Posted: 25 Apr 2011 at 11:59am
|
Hello, I have a field that I need to truncate that looks like this: console6-2APB 3-2-E3 or like this: console6-2OBTRIAGEB 2-1-A5 The part i need is the 2APB or the 2OBTRIAGEB. I know how to do the trim function if there is the same amount of characters that you need to trim from the field. But how would you go about trimming everything after the space (which is a different number of characters in for each value). Thanks for your help! ------------- Thanks! Erin |
Replies:
Posted By: FrnhtGLI
Date Posted: 26 Apr 2011 at 2:19am
|
This is pretty ugly, but it will work. It will remove the 'console6-' and '3-2-E3' or '2-1-A5'. mid((left({table.field},instr({table.field}, ' '))), instr(left({table.field},instr({table.field}, ' ')), '-')+1) ------------- |< /\ '][' ( ) |
Posted By: lockwelle
Date Posted: 26 Apr 2011 at 3:12am
|
this seems less ugly
local numbervar idash = instr({table.field}, "-") + 1;
local numbervar ispace = instr({table.field}, " ");
mid({table.field}, idash + 1, ispace - idash)
|
Posted By: Erinmichelle
Date Posted: 26 Apr 2011 at 4:22am
|
Thank you both so much! It's working now :)
------------- Thanks! Erin |
Posted By: miamitourism
Date Posted: 20 May 2011 at 10:25am
|
I have a similar problem that I thought you might be able to help me with. I have a field which contains an Address1, like "123 Main St." or "12 NE 46th St."
I need to truncate this field so that the field shows only the first two numbers and nothing else. The issue is, of course, that an address can have house numbers that are 2-5 digits long.
Any ideas?
 |
Posted By: lockwelle
Date Posted: 21 May 2011 at 3:02am
| left({table.field},2) |
Posted By: siguy
Date Posted: 09 Jun 2013 at 10:05am
|
Hi All,
I understand that this is an old thread, but would anyone be able to help me with what I think is a similar issue to the first above.
I want to be able to truncate all text on either side of specific characters. A sample of a single entry within this field would be;
01A-Dirty Walls¬
02B-Paint scratched¬
03C-No Toilet paper¬
Can i define a start position e.g. "01A-" and define the end position being either the "¬" or a carriage return. Leaving me with whatever is in between (considering that the text in between could be of varying lengths) and in this case the end result looking like this:
Dirty Walls
Ultimately, what i need is to link items from a different field (i.e. TASK.QUESTIONS), with specific sentences within the field explained above (TASK.NOTES).
Hopefully this would look like this:
Doors/Walls {QUESTIONS} - Walls Dirty {NOTES}
Paint {QUESTIONS} - Paint scratched {NOTES}
Cosumables {QUESTIONS} - No toilet paper {NOTES}
Thanks in advance.
|
Posted By: lockwelle
Date Posted: 11 Jun 2013 at 4:48am
|
local numbervar istart := instr({table.field}, "-");
local numbervar iend := instr(istart + 1, {table.field}, "-"); if istart > 0 and iend > 0 then mid({table.field}, istart + 1, iend - istart - 1) else {table.field} you could probably add a level of complexity, but the instr return -1 or 0 if it cannot find the string that it is searching for (the dash). so if there is a start dash and no ending...this snippet won't work. HTH |
Posted By: siguy
Date Posted: 16 Jun 2013 at 1:04am
|
Thanks Lock,
helps a lot :)
|