nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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?

Find more posts tagged with

Accepted answers

JohannesLindner

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 || '%'

All comments

rozekc_washtenaw

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

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.

JohannesLindner

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 || '%'

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.