String functions

[swhitt]

I need some help on how to use Crystal functions to pull characters out of a string. For example, the string may be as follows:

011
204C;204C;204C;204C;204C;204C
009;010
12

It could be any combination of numbers and/or letters and/or symbols.
I need a formula that pulls out everything to the left of the ; if there is one. If not, I need all characters extracted.
For the second formula, I need everything to the right of the ;
I know how to do this using SQL's InStr and SubString functions but am not sure about Crystal's functions.
Any help would be greatly appreciated.

 

[synapsevampire]

Try:

if instr({table.field},";&quot > 0 then
left({table.field},instr({table.field},";&quot+1)
else
{table.field}

-k

 

[swhitt]

Thank you for your response. That worked perfectly. Now, I just have one more similar issue. I want to suppress all characters in a string that are numeric but show only letters and symbols. For example in the following string:
1643-A
I only want to see the -A. How would I use instr to do this?

 

[synapsevampire]

Since letters could be anywhere in the string, you'd need a loop to do this:

whileprintingrecords;
local stringvar Loopthrough:= {table.field};
local stringvar CharsReturned;
numbervar Counter;
for Counter := 1 to len(trim(Loopthrough)) do(
if chr(upper(mid(Loopthrough,Counter,1))) in [65 to 90] then
CharsReturned := CharsReturned+mid(Loopthrough,Counter,1);
);

Couldn't test, but the theory is sound.

-k

 

[Reebo99]

SV, I think your example will only show A-Z, not symbols.

Try:

whileprintingrecords;
local stringvar Input:= "1643-A";
local stringvar Output;
Local Numbervar Counter = 0;
While Counter <> Len(Input) Do (
Counter := Counter + 1;
If IsNumeric(Mid(Input,Counter,1)) then Output else
Output := OutPut&Mid(Input,Counter,1);
);
Output;

Change the item in bold with your field....

Reebo
UK

&quot;Before you judge a man, walk a mile in his shoes. After that, who cares? ...He's a mile away and you've got his shoes.&quot; - Billy Connolly