cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Visit the PTCooler (the community lounge) to get to know your fellow community members and check out some of Dale's Friday Humor posts! X

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

amatei
12-Amethyst

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

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
awalsh
17-Peridot
(To:amatei)

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

 

 

View solution in original post

5 REPLIES 5
awalsh
17-Peridot
(To:amatei)

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
12-Amethyst
(To:awalsh)

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
17-Peridot
(To:amatei)

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
12-Amethyst
(To:awalsh)

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
17-Peridot
(To:amatei)

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

 

 

Top Tags