[IM] query-problem with "<field> contains <text>", when Text contains "S."

[JensN.]

Hi @all,

we have a shorttext-field "Parameterbezeichnung", which could contain values like "N.17", "B.20", "S.19" and so on. It could also contain combinations of these values, separated by commata. So now we have queries which are filtering over this field, and it works as long as the filtervalue isnt something with "S.". So a querydefinition like

(field["Parameterbezeichnung"] contains "B.20")

is giving me all items where this field contains "B.20", but no item with "N.20" or "B.19" in this field. But the same query with the definition

(field["Parameterbezeichnung"] contains "S.19")

is giving me all items with ".19", so its giving "B.19", "S.19" and "N.19". So whats the matter with this "S."? Is this some kind of placeholder for SQL-statements in the background? We're using Integrity 10.4 with Oracle 11

Thanks, Jens

[tdalon · Accepted solution]

Hi Jens

I got some troubles also with queries and filtering by "contains".

There is some kind of undocumented filter using the LIKE operator.

(field["Your field"] contains "LIKE S.19'")

Maybe this solves your issue.

I would be happy if PTC documents/ explains this.

(For me there is a bug in the default contains filter)

Kind Regards

Thierry

[JensN.]

Hi Thierry,

you saved my day, thank you

It works, but i'm also very interested in an answer of PTC. A complete list of options would be nice

kind regards, Jens

[JoeBartlett]

The other answers in this thread are correct - the text searching functionality of Integrity depends completely on the text searching capabilities of the underlying database (Oracle/SQL). Since Integrity 10.1 (I think) there has been a nice button to see the various text searching options.

In the desktop client:

This is also readily available in the web interface:

In addition to the Integrity Client Getting Started Guide section on text searching, there is an Integrity Help Topic about this in both interfaces and a KCS article here.

[mrump]

Hi Joseph,

to be honest I've never clicked that Button, as I usually use the query create/edit dialog or the CLI.

But now I did, and I do not find any hind regarding the problem described in this thread ( meaning the DOT being treated as a special character aka "symbol").

And there is no hint about the use of LIKE.

Maybe the "Help Button" needs an update

>>> Following is a copy of the text provided by the described Button (from I10.4):

Text Search Rules
Searches short and long text fields only. Symbols such as [], !, #, and @ are ignored. Searching is case-insensitive. For example, java returns items containing java, Java, and JAVA. Adding more words narrows the search, it does not expand it. Meaning, Integrity searches for items that include all of the specified words, not items that include any of the specified words.
Quick Reference
Exact word or phrase	""	Search For: "medical device" Returns: medical device, not medical devices
Multiple words or phrases	AND	Search For: "battery life" AND "battery size" Returns: both phrases, not one or the other
Either word or phrase	OR	Search For: "MD5000" OR "MD5002" Returns: MD5000 or MD5002
Exclude word or phrase	-	Search For: "product lifecycle" -management Returns: product lifecycle, not product lifecycle management
Fuzzy search	~	Search For: ~applied Returns: applied, applies, and apply
Wildcard	*	Search For: except* Returns: exceptional and exception

[KaelLizak]

Unfortunately, Matthias, I believe the "Help Button" doesn't need an update... yet.

There is an RFC to cover this: 960764. Please contact PTC Integrity Support if you wish to be associated with this enhancement request.

Regards,

Kael

[JensN.]

Hi,

didnt recognize this Button until now, and it will helb in some cases, but not in my case

Thanks nevertheless

kind regards, Jens

[tketz]

Hi,

we have a similar problem, but we use MS SQL Server 2008 R2:

I have 3 items with N.10, S.10 and B.10 in the "Keyword" field.

In my query it doesn't matter if i search for

(field["Your field"] contains "LIKE S.10'")

(field["Your field"] contains "LIKE N.10'")

(field["Your field"] contains "LIKE B.10'")

(field["Your field"] contains S.10)

every result is all 3 items.

The Problem seems to be the "." (dot) and i found no way to cascade it.

[mrump]

Hi all,

we've had a similar problem some weeks ago, when the search term included a "@".

In our case the search term (field["my richtext field"] contains foo@bar) found all items with "foo" or bar, while (field["my shorttext field"] contains foo@bar) seemed to work correctly.

For Richtext fields the adding LIKE did the trick.

Discussions with PTC service lead to a "known difficulties" with text searches in integrity, especially when it comes to "special characters".

1. the text search is generally based on a underlying DB system and it's abilities (index refresh rate etc.) and especially the list of "special characters" that effect the result.

In our case: the @ was ignored by the oracle db engine (meaning internally replaced by space) which changed the search key to "foo bar" that is interpreted just like ( (field["my richtext field"] contains foo)OR(field["my richtext field"] contains bar) )

In the past we had also issues with German Umlauts "ä, ö, ü" that could only be worked around using the UTF hex values for the search string.

So to me there is only "Try&Error" to find out what workaround

- LIKE or not

- Hex encoding for characters

- db settings (see "Server Installation and Configuration Guide" chapter "Configuring an Oracle Database for Special Character Searches")

for both, richtext and short text fields.

HTH Matthias

[JensN.]

Hi @all,

in our case this problem is solved. Reason for the problem was a stop list in our oracle database. Here a quote from PTC-Support:

This is a list of "words" that appear frequently or are generally meaningless to search on that the database should not bother to index. (...) If you look at the Text Index definition (output.txt), you'll see that the following letters are being treated as stop words: a,d,i,s,t. Generally, single-letter words (I, a) and anything that could appear as part of a contraction ('t, 'd, 's). The solution to this problem is to remove the A,D,I,S,T letters from the stop word list.

Maybe this will help some other person in a similar case.

kind regards, Jens