How to use definition queries to find where a field (FULL ADDRESS) doesn't include values from another field (UNIT)?

[AndrewWallick]

We're trying to review our data to make sure our FULLADDRESS field includes the value in the UNIT field. They are both Text fields. I have a definition query:
FULLADDRESS NOT LIKE '%UNIT%' And UNIT IS NOT NULL

I believe this should work, but it's showing me records where the unit is 3, and the FULLADDRESS is something like "555 Main St 3". There are 30,000 records returning, it's just all our addresses where the UNIT is not null.

Does anyone know why this is not working, or a different way to find the records where the FULLADDRESS field doesn't include the value we have for UNIT?

[JohannesLindner · Accepted solution]

Does anyone know why this is not working

It's not working because you're searching for the literal string "UNIT", not the value of the field. Your query will filter out values like "123 UNIT", "UNIT 456", "abcUNIT123".

You have to surround the UNIT field's value with '%'. Online search suggests using '+' or 'CONCAT'. These didn't work for me in a file gdb, but '||' did.

UNIT IS NOT NULL AND FULLADDRESS NOT LIKE '%' + UNIT + '%'

UNIT IS NOT NULL AND FULLADDRESS NOT LIKE CONCAT('%', UNIT, '%')

UNIT IS NOT NULL AND FULLADDRESS NOT LIKE '%' || UNIT || '%'

[AndrewWallick]

Thanks so much, that did it! I got the SQL code by first filling in the query builder, choosing "fields" then UNIT, so I just assumed it was correct without much checking.

[ChelseaRozek]

In case it helps anyone else, only this syntax worked for me: NOT FULL_NAME LIKE '%' || NAME || '%'