Wildcard and % in formula

[leftfldr]

Crystal 10 - SQL database. Trying to find vendors that do not have a % sign in their payment terms. I have tried a couple formulas that generally work for me, but are not working in this instance - see below.

not ({PM00200.PYMTRMID} like "*%*")

{PM00200.PYMTRMID} <> "*%*"


Examples of raw data include (there are 30 or more payment terms):
NET 30
2% 10 NET 30
UPON RECEIPT
1% 10 NET 30 VENDORS

I would want NET 30 and UPON RECEIPT to be included on the report.

It must be something easy I am missing. Please advise.

THANKS!

 

[IanWaterman]

When you say SQL database do you mean SQL Server. In Oracle which is a SQL database the wild card is %, thus you will not be able to search for such a character.

not ({PM00200.PYMTRMID} like "*%*") is the correct filter to use.

If it is SQL server you select statement should be OK as wild card is *.

Have a look at the SQL generated by the report and see how it has interpreted your filter above.

Ian

 

[leftfldr]

I apologize. Yes, I do mean SQL Server.

How do I look at the SQL generated by the report as the filter is not working? If I put it in place I get no records returned.

Tara

 

[leftfldr]

I found where to look.

Here is what it says for that part:
WHERE PM00200.PYMTRMID NOT LIKE '%%%'

Why did it turn the * into % signs?

Tara

 

[leftfldr]

Also, funny thing is if I turn it around and do:
({PM00200.PYMTRMID} like "*%*") it DOES get just all records with % signs in the payment terms, but does not work if I do a NOT LIKE. Here is the sql from this:
WHERE PM00200.PYMTRMID LIKE '%%%'.
Any ideas?

 

[gortuk]

Have you tried using an escape character in your select statement?

eg
Not ({PM00200.PYMTRMID} like "*\%*")

 

[leftfldr]

Perfect! That worked.

What does the escape character do?

 

[gortuk]

If you Google "escape character" you'll get a more thorough explanation, but basically an escape character ensures that the following character is not interpreted, but just treated literally (eg "this is just a percent sign, not a wildcard").