Using NOT in String data type

[starbase59]

I have CR9 and SQL Server db.

I want to exclude particular text strings using NOT, and I need to use the wildcard to include spelling errors and/or variations of spelling, etc, within the text string. So far, none of my formulas have worked.

Can NOT("X") be used with character data type?

For example:
not({HISTORY.DESCRIPTION} like "Left VoiceMail*")

If I design it like this with <>:
({HISTORY.DESCRIPTION} <> "Left VoiceMail")
then it will only search for the text, if it's spelled exactly like what is between the quotes, which I don't want.

Any solutions?

 

[dgillz]

Your formula should work perfectly. Have you tried it and failed?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[synapsevampire]

Like and <> are the same in terms of searching foir the same spelling, how they differ is that you must use LIKE to use the wildcard.

If you want to check for speeling erros, you'll need to provide a mask for each:

(
not({HISTORY.DESCRIPTION} like "Left Voice*")
)
and
(
not({HISTORY.DESCRIPTION} like "Left Vioce*")
)

What you are confusing this with is a soundex type of function, as in:

not(soundex({HISTORY.DESCRIPTION}) = soundex("Left Voicemail"))

This won't pass to the database, though you might use a soundex type function on the database side for performance reasons.

-k

 

[starbase59]

Thank you, yes all my version have failed.

Here's a bit more of the story. I'm calculating an "Other" field. For example, where the text string is NOT one of the following:
{HISTORY.COMPLETEDDATE} in lastfullweek
and {HISTORY.TYPE} = 262146 and
(NOT({HISTORY.DESCRIPTION} like "Left VoiceMail*") or
NOT({HISTORY.DESCRIPTION} like "Sales Call*") or
NOT({HISTORY.DESCRIPTION} like "Schedule a meeting*"))
then I want it counted within my "Other" category.

When I use only 1 NOT, then it works. As soon as I add in more, then it fails.

 

[starbase59]

Thank you synapsevampire. Makes sense, this is what I was afraid of. I will try soundex.

 

[lbass]

One issue is that you should be using "and" in your clauses for "not like", ie.

{HISTORY.COMPLETEDDATE} in lastfullweek and
{HISTORY.TYPE} = 262146 and
NOT({HISTORY.DESCRIPTION} like "Left VoiceMail*") and
NOT({HISTORY.DESCRIPTION} like "Sales Call*") and
NOT({HISTORY.DESCRIPTION} like "Schedule a meeting*")

You might also need to check for case in addition to spelling. In that case I would use a SQL expression to convert your description field to upper case, using ucase() and then type in the options all in caps.

-LB

 

[starbase59]

thank, lbass. yes, I meant and, not or.