Diferent results of Contains vs Does not Contain | Community
Skip to main content
Darren_Bowers
Darren_Bowers
Level 9
October 29, 2020
Solved

Diferent results of Contains vs Does not Contain

  • October 29, 2020
  • 1 reply
  • 2755 views

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"

 

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Milan_Vucetic

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

1 reply

Milan_Vucetic
Milan_VuceticAccepted solution
Level 9
October 30, 2020

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
    Darren_BowersAuthor
    Level 9
    November 1, 2020
    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
    Terms & ConditionsAccessibility statement

    Loading footer...