delimited text runs together how can I separate it? 2

[chrisdoesstuff]

Crystal X, SQL 2008
I have an app that sends data to the db as a pipe delimited varchar so when I query the db I get

Jane Doe|John Doe|Mike Smith

In crystal it displays as JaneDoeJohnDoeMikeSmith

Is there a way I can make it Jane Doe John Doe Mike Smith or the more preferable Jane Doe, John Doe, Mike Smith?

Thanks in advance for your help.

 

[IanWaterman]

If the Pipes have been removed there is not a lot you can do.

If the pipes where still there you could use a formula

Replace({yourfield}, "|", ", ")

Ian

 

[chrisdoesstuff]

I was hoping you wouldn't say that... I guess it's string replacement with my friend IF THEN ELSE.

 

[lbass]

The following will work, assuming that names always consist only of two words, with only one capital at the beginning of first name and last name (No "McAdams", e.g.). You MUST go into file->report options and make sure that "database server is case insensitive" is NOT checked.

stringvar x := {table.string};
numbervar i;
numbervar j := len(x);
numbervar cnt := 0;
stringvar y := "";
for i := 1 to j do(
if i > 1 and
x=uppercase(x) then (
cnt := cnt + 1;
if remainder(cnt,2)= 0 then
y := y + ", "+x else
if remainder(cnt,2)=1 then
y := y + " "+x
) else
y := y + x
);
y

-LB

 

[chrisdoesstuff]

Thanks for the formula. Currently the data isn't standardized to always be two names and not be McCarthy, O'Malley or Smith-Jones. I will be taking this and putting it in my file so if I do get standardized data like that I'll be able to work it out.

Thank you again for your help!

 

[lbass]

You could add exceptions to the formula for certain standard possibilities.

-LB

 

[lbass]

With some exceptions:

stringvar x := {table.string};
numbervar i;
numbervar j := len(x);
numbervar cnt := 0;
stringvar y := "";
for i := 1 to j do(
if i > 1 and
x=uppercase(x) then (
cnt := cnt + 1;
if x in ["'","c","-"] or
x[i-1] in ["'","c","-"] then
y := y + x else
if remainder(cnt,2)= 0 then
y := y + ", "+x else
if remainder(cnt,2)=1 then
y := y + " "+x
) else
y := y + x
);
y

-LB