How to extract substring from string in crystal report

Question

I am new to crystal reports.

My data(employee ids) is of the following format

Abc123, uttd333, ddt-435

I want to extract only numbers and remove leading letters and special characters.

Also there are certain values that should never be printed.

Admin Ids such as

Gree999, ttt999

I know there is a mid function but that requires me to specify the position from where the substring should begin. These values don't have a fixed number of leading letters.

Is there anything like Ltrim like we have in SQL that we can use to achieve this in crystal reports?

Answer 1

Afaik there's no built-in function in CR to remove non-numeric characters from a string, so you'll have to roll your own (replace {Befehl.EmployeeId} with your field):

StringVar employeeId := {Befehl.EmployeeId};
StringVar result := "";
NumberVar i;

// transfer numeric chars into result one by one
For i := 1 To Length(employeeId) Do
(
  If IsNumeric(employeeId[i]) Then
     result := result + employeeId[i];
);

// output result only if employeeId is not in your admin list
If employeeId In Split('Gree999,ttt999', ',') Then
  '--Admin--'
Else
  result;