New post
Question
· Jul 7, 2023

%CONTAINS doesn't work with "time" string

#SQL #InterSystems IRIS

Is "time" a reserved word in the %CONTAINS function?

When I run the following SQL query, I get 0 matching results

SELECT ID, text
FROM Test.Sentence
WHERE text %CONTAINS('time') OR text %CONTAINS('time ') OR text %CONTAINS(' time')

However, when I run a query where the search string contains words other than "time" alone, it returns the expected matching results

SELECT ID, text
FROM Test.Sentence
WHERE text %CONTAINS('providers time money') OR text %CONTAINS('providers time') OR text %CONTAINS('time money')

It also works if I try to search for "tim" or "ime"

SELECT ID, text 
FROM Test.Sentence 
WHERE text %CONTAINS('tim') OR text %CONTAINS('ime')
Product version: IRIS 2020.1
Discussion (7)1
Log in or sign up to continue
Julius Kavay · Jul 7, 2023

There are no reserved words for %Contains but there is a requirement on the field (property) type. Excuse me for my arrogance, but instead of reading the documentation everybody thinks, it's cheaper to ask a question in the developer community, to get a ready made solution. So please read this first and if something is not clear, then tell it us what you do not understand and we will help you to understand.

2 0
Vivian Lee · Jul 7, 2023

Thank you for the responses. To clarify, I did read the documentation for %CONTAINS as a starting point before posting this question on the developer community.

The datatype of the column (property) I am using %CONTAINS on is of %Text and the LANGUAGECLASS is set to a custom class that extends %Text.English. 

Perhaps there is something in the docs that I'm missing that would cause the issue with the searching for the "time" word I'd mentioned above.

However, it seems like @Robert Cemper 's suggestion with using the [ operator achieves the same functionality I'm looking for with a similar performance time - when tested in the SQL page of the management portal.

0 0
Robert Cemper · Jul 8, 2023

Hi @Vivian Lee !
I think you had no chance to see the problem in docs
You mentioned a custom class that extends %Text.English
this might be the cause of your problem.
The class is not visible in normal class reference (don't ask me why) 

Default %Text.English has a Parameter  NOISEWORDS100 =
"the of and a to in is you that it he for was on are as with his they at be this from I have or by one had not but what all were when we there can an your which their said if do will each about how up out them then she many some so these would other into has more her two like him see time could no make than first been its who now my made over did down only way find use may long little very after called just where most know get through back";

So you might be affected by these NOISEWORDS.
I failed to find any useful public documentation on this parameter and also %Text.Text is not too useful

4 0
Julius Kavay · Jul 8, 2023

Somehow I don't understand your problem. If I create a simple class with a %Text property, insert some text data and then query for the word "time" I get it without problem.

Class DC.TextData Extends %Persistent
{
Property Comment As %Text(LANGUAGECLASS = "%Text.English", MAXLEN = 1000);
}

Now some data and the query

USER>d ##class(DC.TextData).%KillExtentData()

USER>s tt=##class(DC.TextData).%New(),tt.Comment="Notice, time is money." w tt.%Save()
1
USER>s tt=##class(DC.TextData).%New(),tt.Comment="It's time for a break." w tt.%Save()
1
USER>s tt=##class(DC.TextData).%New(),tt.Comment="There is no business like show business." w tt.%Save()
1
USER>d $system.SQL.Shell()
SQL Command Line Shell
----------------------------------------------------

The command prefix is currently set to: <<nothing>>.
Enter <command>, 'q' to quit, '?' for help.
[SQL]USER>>select $zversion
5.      select $zversion

Expression_1
IRIS for UNIX (Ubuntu Server LTS for x86-64) 2021.2 (Build 649U) Thu Jan 20 2022 08:49:51 EST

1 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.0006s/4/140/0ms
          execute time(s)/globals/cmds/disk: 0.0008s/0/394/0ms
                          cached query class: %sqlcq.USER.cls7
---------------------------------------------------------------------------
[SQL]USER>>select * from DC.TextData where Comment %CONTAINS('time')
6.      select * from DC.TextData where Comment %CONTAINS('time')

ID      Comment
1       Notice, time is money.
2       It's time for a break.

2 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.0005s/4/140/0ms
          execute time(s)/globals/cmds/disk: 0.0043s/30/4,322/0ms
                          cached query class: %sqlcq.USER.cls2
---------------------------------------------------------------------------
[SQL]USER>>quit

USER>
0 0