Case insensitive, partial match on string field using feature effect.

[AndrewMurdoch1]

Good Day

I want to perform a feature effect query that is both case insensitivity and partial.  If the name field has "HELLO" in it, I want to it match any variation of HELLO, hello, he, etc...

The best I can get is doing:

name LIKE '%HELLO%'

Which will find all occurrences, and combinations of HELLO, (HE, HEL, etc...), but when I try to add case insensitivity it doesn't work.

How do you make that case-insensitive?  I have to do this using a feature effect query because the layer data I'm looking at, is from a StreamLayer, so I have to query the LayerView,

I thought doing this might have worked

(name LIKE UPPER('%hel%'))

But I get an error about UPPER, so now I think the best way to tackle this issue is by asking the Stream for a feature update. 

Thanks

[JoelBennett · Accepted solution]

It appears the where clause is parsed and applied client-side, and if it doesn't support functions like UPPER and LOWER, then it doesn't appear the SDK can natively do what you want.

However, it is possible to apply this kind of filtering by adding your own functionality to add conditions for every combination.  It's not pretty but it works, although I'm not sure what performance would be like as search values get longer and longer, since there would 2-to-the-power-of-x conditions where x is the number of characters in the input string.

The following set of functions gets the job done:

function addWordCombinations(word, index, partial, wordCombinations) {
	if (index < word.length) {
		addWordCombinations(word, index + 1, partial + word[index].toUpperCase(), wordCombinations);
		addWordCombinations(word, index + 1, partial + word[index].toLowerCase(), wordCombinations);
	} else
		wordCombinations.push(partial);
}

function getWordCombinations(word) {
	var wordCombinations = [];

	if ((typeof word == "string") && (word.length !== 0))
		addWordCombinations(word, 0, "", wordCombinations);

	return wordCombinations;
}

function getCaseInsensitiveQueryConditions(fieldName, userValue) {
	var wordCombinations = getWordCombinations(userValue);

	if (wordCombinations.length === 0)
		return "1=0"; //query with this where clause would return no records
	else
		return "(" + fieldName + " LIKE '%" + wordCombinations.join("%' OR " + fieldName + " LIKE '%") + "%')";
}

With those in place, and assuming the user-specified search value was stored in a variable called "userValue" and the attribute field name was "name", you'd have something like:

featureFilter.where = getCaseInsensitiveQueryConditions("name", userValue);

Note: this implementation assumes the user-specified search value doesn't contain apostrophes (i.e. single quotes).

Also, looking back after already typing up everything above, I notice you say you tried:

(name LIKE UPPER('%hel%'))

but have you tried:

(LOWER(name) LIKE '%hel%')

 If that works it would be so much simpler...

[AndrewMurdoch1]

Good Morning

Please excuse the delay in response, I thought I responded last week.

That is what I thought, the added complexity of doing this is not worth the headache, and it just creates more ways in which my filter can break.  I was believing that the queries supported SQL92 syntax, which does not appear to be the case, and I can't remember where I read that.

I think the best course of action is to have our backend introduce querying, and since I'm using the Stream Layer, they can just send me updated information.

Cheers.