Setting very specific exclusion in Query widget

Question

Hi there,

I have a very specific request that I'm struggling with. Within my map, I have a layer which contains a PERIOD field, which for complex reasons contains multiple values e.g. 'ROMAN\EARLY MEDIEVAL\LATE MEDIEVAL\MEDIEVAL\POST MEDIEVAL'. Because this is a multiple entry, the only way to search is by using 'CONTAINS'. Unfortunately, whilst someone looking for features of a 'BRONZE AGE' date, who enters 'BRONZE AGE' as their criteria, will be happy to find 'EARLY BRONZE AGE', 'LATE BRONZE AGE' as well as just 'BRONZE AGE', POST MEDIEVAL  is a different time period. Therefore, someone looking for 'MEDIEVAL', although happy to find 'LATE MEDIEVAL' as well as just 'MEDIEVAL' will not want 'POST MEDIEVAL'. I've been trying to find a way to exclude 'POST MEDIEVAL', but everything I've tried also excludes 'MEDIEVAL'. I guess what I need is the query 'CONTAINS 'POST MEDIEVAL' AND DOES NOT CONTAIN 'MEDIEVAL\POST MEDIEVAL', but used as an excluder. BTW I know that because of the structure, if both 'MEDIEVAL' and 'POST MEDIEVAL' occur they will always occur as 'MEDIEVAL\POST MEDIEVAL'  so that string is searchable.

Does that make sense?

Thanks

SimonCrutchley · Answer

Unfortunately I have c200,000 entries, some of which include MEDIEVAL, some POST MEDIEVAL, some both, and some none, so going back to edit the data isn't really an option :( . I'd also need to ask users to search using /MEDIEVAL/ instead of just entering the period, which is tricky.

Thanks for your help in trying.

JohannesLindner · Answer

Aaah, now I get the problem...

Yeah, this is going to be tricky. I tried a few things and couldn't find a good solution, but maybe someone more versed in SQL than me can.

If you're willing/able to edit your field, there would be a simple solution:

* Make sure that "MEDIEVAL" always has the slashes at the start and end* e.g. have the logic that is filling that field insert the slashes at the start and end of the string, too

* search for CONTAINS '/MEDIEVAL/'

SimonCrutchley · Answer

Hi Johannes,

Sorry, I haven't explained clearly enough. This will exclude those examples which are both MEDIEVAL and POST MEDIEVAL, which is not what I need. Unfortunately, I need to have those examples where it is both, but not just POST MEDIEVAL.

Sorry