Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Truncating based on character rather than length Post Reply Post New Topic
Author Message
Erinmichelle
Newbie
Newbie
Avatar

Joined: 06 Nov 2009
Location: United States
Online Status: Offline
Posts: 24
Quote Erinmichelle Replybullet Topic: Truncating based on character rather than length
    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!



Edited by Erinmichelle - 25 Apr 2011 at 12:06pm
Thanks!

Erin
IP IP Logged
FrnhtGLI
Senior Member
Senior Member
Avatar

Joined: 22 May 2009
Online Status: Offline
Posts: 347
Quote FrnhtGLI Replybullet 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)
|< /\ '][' ( )
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet 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)


Edited by lockwelle - 26 Apr 2011 at 3:13am
IP IP Logged
Erinmichelle
Newbie
Newbie
Avatar

Joined: 06 Nov 2009
Location: United States
Online Status: Offline
Posts: 24
Quote Erinmichelle Replybullet Posted: 26 Apr 2011 at 4:22am
Thank you both so much! It's working now :)
Thanks!

Erin
IP IP Logged
miamitourism
Newbie
Newbie
Avatar

Joined: 14 Jan 2011
Online Status: Offline
Posts: 24
Quote miamitourism Replybullet 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?
 
Smile
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 21 May 2011 at 3:02am
left({table.field},2)
IP IP Logged
siguy
Newbie
Newbie
Avatar

Joined: 29 Sep 2012
Online Status: Offline
Posts: 10
Quote siguy Replybullet 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.
 
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet 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
IP IP Logged
siguy
Newbie
Newbie
Avatar

Joined: 29 Sep 2012
Online Status: Offline
Posts: 10
Quote siguy Replybullet Posted: 16 Jun 2013 at 1:04am
Thanks Lock,
 
helps a lot :)


Edited by siguy - 27 Jun 2013 at 8:47am
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.039 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.