Solved

Formula to only display certain character in a string

Posted on 2004-09-30
8
712 Views
Last Modified: 2012-08-14
In Crystal Reports 8.5, I have a field that outputs the client's current location as such: "sicu02 (SICU)"  this all comes from the same databse field. In Crystal I only want to display (SICU). The ( ) are always going to be part of the string but do not always start or end in the same positions. Has anyone done this before? Thanks.
0
Comment
Question by:zalezivy25
8 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 12193281
So you want to display (Whatever is here)

Create a formula

@Location
numbervar leftparen;
numbervar rightparen;

leftparen := instr({Table.Field}, "(");
rightparen := instr({Table.Field}, ")");
midstr({Table.Field},leftparen,rightparen-leftparen+1);

mlmcc
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 12193404
Or you can use the ExtractString function:

ExtractString({table.field},"(",")")

~Kurt
0
 
LVL 26

Accepted Solution

by:
Kurt Reinhardt earned 250 total points
ID: 12193550
There are a lot of ways to do this.  My function won't return the parentheses, but I figured the really important data you wanted returned is what is contained within the parentheses, "SICU".

mlmcc's formula returnst the parentheses if you want them, but there are some flaws.  Following is the corrected code:

//@Location
numbervar leftparen;
numbervar rightparen;
stringvar parenval;

leftparen := instr({@test}, "(");
rightparen := instr({@test}, ")");

parenval := mid({@test},leftparen,rightparen-leftparen+1);



If the parenthetical value is always at the end of the string, you could also use the following code, which is simpler:

Mid({table.field}, Instr({table.field},"(") -1)



If the parenthetical value is always at the end of the string AND there's always a space before it, you could use the following (even simpler than the code above):

Mid({table.field}, Instr({table.field}," "))


~Kurt


0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 12193618
btw, you don't need to declare the extra variable in my version of mlmcc's formula (I tested it incorrectly) - the only error was the Midstr function, which should have been Mid:

//@Location
numbervar leftparen;
numbervar rightparen;

leftparen := instr({@test}, "(");
rightparen := instr({@test}, ")");

mid({@test},leftparen,rightparen-leftparen+1);

~Kurt
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 28

Expert Comment

by:bdreed35
ID: 12193635
I am ususally for the simplest, most straight forward approach so I like the rhinok's method.
If you need the paren's to display, just conactenate them on there:

"(" & ExtractString({table.field},"(",")") & ")"
0
 
LVL 10

Expert Comment

by:ebolek
ID: 12194301
I am not going to add another string function. How do you guys remember these? I always have to look at the help before i use these functions. You guys are amazing. Too much knowledge
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 12195680
As you can see we sometims get them wrong as in our use of midstr.

In my case it is probably years of programming and use of the functions.

mlmcc
0
 
LVL 1

Author Comment

by:zalezivy25
ID: 12214231
Thank you for your help
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now