Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Formula to only display certain character in a string

Posted on 2004-09-30
8
Medium Priority
?
725 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 1000 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

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…
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…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

722 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