Query problem with "<field> contains <text>"

[amatei]

Hi,

We are using Integrity 11.0.0.506. 

In Workflow and Documents I would like to find all items where the "Summary" field contains "pca"

The query is -> Summary contains "pca"

But the result is incorrect

It returns only the item where Summary is exactly equal to "pca" ?!

It is missing the items where "pca" is in the middle or at the beginning of the Summary field text

Please see the attached image.

Only if I write the query as -> Summary contains "pca*" , then I get the expected result

[awalsh]

The contains search will search the text field for any word that matches. In your example, "pca" will match "pca" but it would also match "pca project" "what is pca for" and so on.  It does not match parts of words, though, unless you use the wildcard.

[amatei]

Hi,

Can you tell me why these queries:

Summary contains "(pca"

Summary contains "_pca"

Summary contains "[pca"

Summary contains "{pca"

also match the "pca" string ?

[awalsh]

The characters ( _ [ { are all word or token breakers. When a table is indexed for text searching, the database breaks up the text into tokens using the defined token breakers. The defined list of token breakers depends on the language and database, but in the list usually includes any punctuation and spaces.  The tokens are indexed - so in your case each search would find "pca" since the punctuation character will be ignored. 

For more information:

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/ccapp/indexing-with-oracle-text.html#GUID-63BD819E-5167-48B7-B320-ECEB55C9B5CF 

https://docs.microsoft.com/en-us/sql/relational-databases/search/full-text-search?view=sql-server-2016

[amatei]

Another case:

If a query is written like: Rubric contains "_IN" , the result is incorrect, nothing is returned

Only if the query is written like: Rubric contains "LIKE _IN", the result is correct.

Please see the attached image.

This detail is mentioned here:

https://community.ptc.com/t5/Integrity/IM-query-problem-with-quot-lt-field-gt-contains-lt-text-gt-quot/m-p/81214#M908

[awalsh · Accepted solution]

The LIKE keyword perform the search without indexes for the exact text including characters that would not be found normally.  There's a more detailed explanation of the keywords you can use in searches in the Help Center, under User Help > Tracking Changes With Integrity Lifecycle Manager Items > Searching for Items > Enhancing a Text Search for Items Using Operators