%CONTAINS doesn't work with "time" string
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')Comments
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.
Agree with @Julius Kavay
Just read the documentation on %CONTAINS
It's a function specific to data type %TEXT and is language dependant (more details in docs)
-------------------
If you look for a functionality as SQL LIKE you better use LIKE
or [ the contains operator of COS
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.
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
Thank you, Robert! I think you are right about those NOISEWORDS. I tested against other words in that set and noticed the same behavior where %CONTAINS did not return results but the [ operator did. Appreciate the follow-up and detailed explanation!
if you want to take a look behind the scene try this example
Character-Slice Index
Quick Summary:
- [ looks for any character(s)
- %CONTAINS looks for words (separated by blanks or punctuations)
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 $zversion5. select $zversion
Expression_1
IRIS for UNIX (Ubuntu Server LTS for x86-64) 2021.2 (Build 649U) Thu Jan 20202208: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>