Select to view content in your preferred language

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

1388
3
Jump to solution
08-11-2023 09:59 AM
AndrewWallick
Frequent Contributor

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?

Tags (3)
1 Solution

Accepted Solutions
JohannesLindner
MVP Frequent Contributor

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

 


Have a great day!
Johannes

View solution in original post

3 Replies
JohannesLindner
MVP Frequent Contributor

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

 


Have a great day!
Johannes
AndrewWallick
Frequent Contributor

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.

0 Kudos
ChelseaRozek
MVP Regular Contributor

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

0 Kudos