Solved

Formula to only display certain character in a string

Posted on 2004-09-30
8
722 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 101

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
On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

 
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
 
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 101

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Suggested Courses

636 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