Filter based on search words

[mattneo]

I have thousands of rows of subject titles. I only want those contain, say "cat", in the titles. The text input has a search parameter embedded. The list table as the following advanced filter: 

( UpCase('SBJCT_TITLE'n) Contains UpCase(Concatenate(

Concatenate(' ', 'Search'p), ' ')) ) OR ( UpCase(Substring(

'SBJCT_TITLE'n, 1, GetLength('Search'p))) = UpCase(

'Search'p) ) OR ( UpCase('SBJCT_TITLE'n) EndsWith UpCase

(Concatenate(' ', 'Search'p)) )

The red is for words in the middle, the green for words at the start of the title and the blue for words at the back. However, as you can see in the below picture, it is not what i expected exactly. Titles with "cat" do show up, but words like "catherine" and "catcher" also shows up. Also, the above formula is not perfect as well; what if the title has "cats", instead of "cat"? Any help is appreciated.

[Sam_SAS]

Hi Matt,

As far as I can tell, concatenating spaces to a string does not work. I think this has to do with automatic cleanup of leading and trailing spaces.

You could check each word using GetWord(), but that would be very tedious (you would be doing something like:

(((UpCase(GetWord('TXT_CASE_SBJCT_TITLE'n, 1) = UpCase('Search'p))

OR ((UpCase(GetWord('TXT_CASE_SBJCT_TITLE'n, 2) = UpCase('Search'p))

OR ((UpCase(GetWord('TXT_CASE_SBJCT_TITLE'n, 3) = UpCase('Search'p))

And so on up to the number of words you want to support (possibly a lot!)

I checked to see if you could use Substring() with FindString() to retrieve the characters before and after the match, but it looks like any space characters get cleaned up when you do this. (You can't do StartsWith(' ')).

Possibly someone else can offer another workaround, but I think it may not be possible for your "cat" search to return only "cat" and not "catcher" or "bobcat" unless you do a crazy expression based on GetWord().

Sam