Question Re: Replace internal spaces in string in Technology Q&A

Replace internal spaces in string

Former Member — Tue, 16 Feb 2016 18:26:03 GMT

I have only recently begun using Crystal Reports 2013. I am having a problem with eliminating errors when creating formulas. I have a database field which is a composite of a fixed length Last Name, First Name and Middle Initial. Because it was fixed length the last name is followed by multiple spaces prior to adding the first name. I would like to remove all the redundant spaces and replace them with a comma and single space. The Replace function, by itself, cannot give me what I need. I have tried multiple times to employ a Do While or a While End While loops to to surround the Replace function, but I continue to receive the same error message of: "The keyword 'do' is missing" Obviously, the keyword is not missing regardless of what case the statement uses. I am at a loss to get the Do While loop to work. I have attempted many different variations and all of them throw this same error. The last formula I attempted looks like this:

Do While {CFTVMA2000.PrvName}.Contains(" ")

Replace({CFTVMA2000.PrvName}," ",", "

Loop

I realize that the full syntax may not be correct but I cannot get past the missing 'do' error!

Re: Replace internal spaces in string

abhilash_kumar — Tue, 16 Feb 2016 18:57:02 GMT

Hi Ron,

What is the fixed length of the last name field?

Also, do first names and initials also have fixed lengths?

Could you paste an example string as well?

-Abhilash

Re: Replace internal spaces in string

abhilash_kumar — Tue, 16 Feb 2016 19:04:48 GMT

Please try using this formula and let me know if this works:

local stringvar ss := {CFTVMA2000.PrvName};

local stringvar array arr := split(ss,' ');

local numbervar i;

local stringvar fin;

for i := 1 to ubound(arr) do

(

if i = 1 AND (arr[i] <> ' ' or arr[i] <> '') then

fin := arr[i] + ', '

else if arr[i] <> ' ' or arr[i] <> '' then

fin := fin + arr[i] + ' ';

);

fin;

-Abhilash

Re: Replace internal spaces in string

Former Member — Tue, 16 Feb 2016 19:41:54 GMT

THANK YOU! What you sent worked just fine. I was not sure of the original field sizes were but would appear to be around 13-14 characters. Still I have the question of why the Do While loop does not work and always states the 'do' is missing?