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

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

ID: 12193404
Or you can use the ExtractString function:

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

~Kurt
0

LVL 26

Accepted Solution

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

LVL 26

Expert Comment

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

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

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

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

ID: 12214231
Thank you for your help
0

## Featured Post

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…
###### Suggested Courses
Course of the Month9 days, 23 hours left to enroll

#### 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.