Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 728
  • Last Modified:

Formula to only display certain character in a string

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
zalezivy25
Asked:
zalezivy25
1 Solution
 
mlmccCommented:
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
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
Or you can use the ExtractString function:

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

~Kurt
0
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
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
 
bdreed35Commented:
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
 
ebolekCommented:
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
 
mlmccCommented:
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
 
zalezivy25Author Commented:
Thank you for your help
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Tackle projects and never again get stuck behind a technical roadblock.
Join Now