Diferent results of Contains vs Does not Contain

[#1]

Hi All - found something weird that we couldn't explain the other day. Testing out a simple query using Contains and the opposite Does not Contain gives different results.

As you can see below, the full data set is 42,356 records. A query of lowercase(first name) that contains the text string "deceased" returns 7 records (this is correct). However, if we apply the query in the opposite fashion it returns 40,053 records. There was an extra  2,296 records it was excluding from the result set. It turns out that these extra records were blank first names.

So my question is why does the query for Does not Contain exclude blank strings in the result? Surely a blank string Does not contain the string "deceased"

[Darren_Bowers · Accepted solution]

Hi @Darren_Bowers 

this is not related to AC but to underlying database.

Contain/does not contain are translated to this:

SELECT * FROM yourTable R0 WHERE (R0.sFirstName LIKE '%' || 'deceased' || '%' ESCAPE '\')
SELECT * FROM yourTable R0 WHERE (R0.sFirstName NOT LIKE '%' || 'deceased' || '%' ESCAPE '\')

If you run these queries directly on database the second query will not count records with empty name string.

Regards,

Milan

[Darren_Bowers · Accepted solution]

Thanks Milan! Do you know if there is a way to force the database to include blank records when it does the query? Right now I am running the query using the "does contain" and excluding it, which is ok but still two steps I'd like to consolidate. Cheers Darren

[Milan_Vucetic · Accepted solution]

Hi @Daren_Bowers, not realy to force but you may use function Coalesce or NVL to replace empty string with some dummy value and then these records will be counted. Check definition of these functions.Regards, Milan