Multiple Like Statements

[sardine53]

Hellow,

I am using Crystal XI with SQL database.

There is a field {CONTACT.NAME} that may contain 2 names:

Jones/Smith
Smith
Jones
Evans/Jones

I want the name to appear each time it qualifies. Many would be double-counted.

Using If/then/else doesn't work becaue it only evaluates in order.

If {CONTACT.LAST} like "*Jones*" then "Tom Jones" else
If {CONTACT.LAST} like "*Smith*" then "Sue Smith" else
If {CONTACT.LAST} like "*Evans*" then "Lee Evams" else
""

What way would I do this?

Thanks,

 

[SkipVought]

hi,

Correct the source data, or create a corrected source from the data. Conceivably, this are a mere handful compared to all the problems.

Data cleanup can be a slow tedious and COSTLY process. Have the problems with editing new data (add and change interface) in the table been fixed first? THAT is the real problem, and it should be addressed before data is cleaned up. Once the door is shut, and no new problems of this type can be generated, THEN it is possible to do some analysis to categorize data problems and begin fixing the table.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[sardine53]

Unfortunately, the field is like this by design. I'm just trying to pull a summary report using the existing data.

Also, I made an error in my example. It should be:


If {CONTACT.NAME} like "*Jones*" then "Tom Jones" else
If {CONTACT.NAME} like "*Smith*" then "Sue Smith" else
If {CONTACT.NAME like "*Evans*" then "Lee Evams" else
""

Thanks,

 

[SkipVought]

That is a VERY POOR design. Probably not designed in consulatation with a database professional.

How many possibilities are there? You have posted only three.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[sardine53]

Approx 10. I know, poor design but it's a legacy database that can't be changed.

 

[SkipVought]

Could there ever be MORE than 'Approx 10'?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Just add a table like this to your database...

CONTACT      NAME
Jones/Smith  Tom Jones
Smith        Sue Smith
Jones        Tom Jones
Evans/Jones  Tom Jones
Jones/Smith  Sue Smith
Evans/Jones  Lee Evans

then join them when you query for your report...

SELECT b.NAME, a.*

FROM 
  Contacts a
, Names b

WHERE a.CONTACT.NAME = b.CONTACT

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lbass]

You could also try a nested replace function, assuming you are always working with the same set of names:

replace(replace(replace({table.name},"Jones","Tom Jones"),"Smith","Sue Smith"),"Evans","Lee Evans")

-LB