I have a table containing a memo field with an FTS index on it. In this table I have three records each with the contents of the memo field following:

Hello world Hello all !! This team are in a soccer world championship !! Hi!! This world are so cute !! These tell good morning to all !!

I try to look for "Hello world" with the CONTAINS command like this: CONTAINS(MemoField, '* * Hello world')

However my three records out in the result. Even with NEAR in CONTAINS, the result out more than one record.

Someone would have a suggestion

asked 08 Oct '15, 19:06

Marius%20Cere's gravatar image

Marius Cere
161182025
accept rate: 0%


without quotes, all tokens are separated. Try following:

SELECT * FROM [mytable]
  WHERE CONTAINS(*,'"Hello World"')

link

answered 09 Oct '15, 17:18

Joachim%20Duerr's gravatar image

Joachim Duerr
8.7k1031133
accept rate: 18%

I have 3 records :

  1. Hello world!!

  2. Hello all!! This team are in a soccer world championship!!

  3. Hello!! This world are so cute !! These tell good morning to all!!

So, each record is an RTF text and if I search with CONTAINS(MemoField, '"Hello world"'), no record is in the result set. If use CONTAINS(MemoField', '"Hello world"'), the first and the third record is in the result set.

Best regards

(12 Oct '15, 16:45) Marius Cere
create table #mytable (id autoinc, memofield memo);
insert into #mytable(memofield) values('Hello world!!');
insert into #mytable(memofield) values('Hello all!! This team are in a soccer world championship!!');
insert into #mytable(memofield) values('Hello!! This world are so cute !! These tell good morning to all!!');

select count(*) from #mytable where contains(memofield,'Hello World');  // ->3
select count(*) from #mytable where contains(memofield,'"Hello World"');  // ->1

Q.E.D.

link

answered 13 Oct '15, 02:44

Joachim%20Duerr's gravatar image

Joachim Duerr
8.7k1031133
accept rate: 18%

For me, in Advantage 10.10.0.49 or 11.10.0.15, the result is :

select count(*) from #mytable where contains(memofield,'Hello World'); // ->1

select count(*) from #mytable where contains(memofield,'"Hello World"'); // ->0

What it is the version you are using?

(15 Oct '15, 17:56) Marius Cere
create table mytable (id autoinc, memofield memo);

insert into mytable(memofield) values('{\rtf1\ansi\ansicpg0\uc0\deff0\deflang0\deflangfe0{\fonttbl{\f0\fnil Arial;}{\f1\fnil\fcharset0 arial;}}{\colortbl;\red0\green0\blue0;\red0\green0\blue255;\red0\green255\blue255;\red0\green255\blue0;\red255\green0\blue255;\red255\green0\blue0;\red255\green255\blue0;\red255\green255\blue255;\red0\green0\blue128;\red0\green128\blue128;\red0\green128\blue0;\red128\green0\blue128;\red128\green0\blue0;\red128\green128\blue0;\red128\green128\blue128;\red192\green192\blue192;}{\stylesheet{\s0\fi0\li0\ql\ri0\sb0\sa0 Paragraph Style;}{\s1\fi0\li0\qc\ri0\sb0\sa0 Centered;}{\s2\fi0\li0\ql\ri0\sb0\sa0 Protected;}{\s3\fi0\li75\ql\ri0\brdrbtw\brdrl\brsp150\brdrs\brdrcf2\brdrw45\sb0\sa0 EmailReply;}{\s4\fi0\li0\ql\ri0\sb0\sa0 ReadOnlyNoReturn;}{\s5\fi0\li0\ql\ri0\sb0\sa0 ReadOnly;}{\*\cs6\f0\fs18\cf1\StyleNameNormal Normal;}{\*\cs7\f0\b\fs20\cf2\StyleNameHeading Heading;}{\*\cs8\f0\b\fs20\cf9\StyleNameSubheading Subheading;}{\*\cs9\f0\i\fs20\cf13\StyleNameKeywords Keywords;}{\*\cs10\f0\ul\fs20\cf11\StyleNameJump1 Jump 1;}{\*\cs11\f0\ul\fs20\cf11\StyleNameJump2 Jump 2;}{\*\cs12\f0\fs18\cf1\Protect\StyleNameProtected Protected;}{\*\cs13\f0\fs18\cf1\ProtectLocked\StyleNameProtectedLocked ProtectedLocked;}{\*\cs14\f0\strike\fs18\cf1\Protect\StyleNameStrikeOut StrikeOut;}{\*\cs15\f0\strike\fs18\cf1\ProtectLocked\StyleNameStrikeOutLocked StrikeOutLocked;}{\*\cs16\f0\fs18\cf1\Protect\StyleNameReadOnly ReadOnly;}{\*\cs17\f0\fs18\cf15\Protect\StyleNameVisitePlanifie VisitePlanifie;}}' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + '\pard\s0\fi0\li0\ql\ri0\sb0\sa0\itap0 \plain \f0\fs18\StyleNameNormal Hello world\par}');

insert into mytable(memofield) values('{\rtf1\ansi\ansicpg0\uc0\deff0\deflang0\deflangfe0{\fonttbl{\f0\fnil Arial;}{\f1\fnil\fcharset0 arial;}}{\colortbl;\red0\green0\blue0;\red0\green0\blue255;\red0\green255\blue255;\red0\green255\blue0;\red255\green0\blue255;\red255\green0\blue0;\red255\green255\blue0;\red255\green255\blue255;\red0\green0\blue128;\red0\green128\blue128;\red0\green128\blue0;\red128\green0\blue128;\red128\green0\blue0;\red128\green128\blue0;\red128\green128\blue128;\red192\green192\blue192;}{\stylesheet{\s0\fi0\li0\ql\ri0\sb0\sa0 Paragraph Style;}{\s1\fi0\li0\qc\ri0\sb0\sa0 Centered;}{\s2\fi0\li0\ql\ri0\sb0\sa0 Protected;}{\s3\fi0\li75\ql\ri0\brdrbtw\brdrl\brsp150\brdrs\brdrcf2\brdrw45\sb0\sa0 EmailReply;}{\s4\fi0\li0\ql\ri0\sb0\sa0 ReadOnlyNoReturn;}{\s5\fi0\li0\ql\ri0\sb0\sa0 ReadOnly;}{\*\cs6\f0\fs18\cf1\StyleNameNormal Normal;}{\*\cs7\f0\b\fs20\cf2\StyleNameHeading Heading;}{\*\cs8\f0\b\fs20\cf9\StyleNameSubheading Subheading;}{\*\cs9\f0\i\fs20\cf13\StyleNameKeywords Keywords;}{\*\cs10\f0\ul\fs20\cf11\StyleNameJump1 Jump 1;}{\*\cs11\f0\ul\fs20\cf11\StyleNameJump2 Jump 2;}{\*\cs12\f0\fs18\cf1\Protect\StyleNameProtected Protected;}{\*\cs13\f0\fs18\cf1\ProtectLocked\StyleNameProtectedLocked ProtectedLocked;}{\*\cs14\f0\strike\fs18\cf1\Protect\StyleNameStrikeOut StrikeOut;}{\*\cs15\f0\strike\fs18\cf1\ProtectLocked\StyleNameStrikeOutLocked StrikeOutLocked;}{\*\cs16\f0\fs18\cf1\Protect\StyleNameReadOnly ReadOnly;}{\*\cs17\f0\fs18\cf15\Protect\StyleNameVisitePlanifie VisitePlanifie;}}' +  CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + '\pard\s0\fi0\li0\ql\ri0\sb0\sa0\itap0 \plain \f0\fs18\StyleNameNormal Hello all !! This team are in a soccer world championship !!\par}');

insert into mytable(memofield) values('{\rtf1\ansi\ansicpg0\uc0\deff0\deflang0\deflangfe0{\fonttbl{\f0\fnil Arial;}{\f1\fnil\fcharset0 arial;}}{\colortbl;\red0\green0\blue0;\red0\green0\blue255;\red0\green255\blue255;\red0\green255\blue0;\red255\green0\blue255;\red255\green0\blue0;\red255\green255\blue0;\red255\green255\blue255;\red0\green0\blue128;\red0\green128\blue128;\red0\green128\blue0;\red128\green0\blue128;\red128\green0\blue0;\red128\green128\blue0;\red128\green128\blue128;\red192\green192\blue192;}{\stylesheet{\s0\fi0\li0\ql\ri0\sb0\sa0 Paragraph Style;}{\s1\fi0\li0\qc\ri0\sb0\sa0 Centered;}{\s2\fi0\li0\ql\ri0\sb0\sa0 Protected;}{\s3\fi0\li75\ql\ri0\brdrbtw\brdrl\brsp150\brdrs\brdrcf2\brdrw45\sb0\sa0 EmailReply;}{\s4\fi0\li0\ql\ri0\sb0\sa0 ReadOnlyNoReturn;}{\s5\fi0\li0\ql\ri0\sb0\sa0 ReadOnly;}{\*\cs6\f0\fs18\cf1\StyleNameNormal Normal;}{\*\cs7\f0\b\fs20\cf2\StyleNameHeading Heading;}{\*\cs8\f0\b\fs20\cf9\StyleNameSubheading Subheading;}{\*\cs9\f0\i\fs20\cf13\StyleNameKeywords Keywords;}{\*\cs10\f0\ul\fs20\cf11\StyleNameJump1 Jump 1;}{\*\cs11\f0\ul\fs20\cf11\StyleNameJump2 Jump 2;}{\*\cs12\f0\fs18\cf1\Protect\StyleNameProtected Protected;}{\*\cs13\f0\fs18\cf1\ProtectLocked\StyleNameProtectedLocked ProtectedLocked;}{\*\cs14\f0\strike\fs18\cf1\Protect\StyleNameStrikeOut StrikeOut;}{\*\cs15\f0\strike\fs18\cf1\ProtectLocked\StyleNameStrikeOutLocked StrikeOutLocked;}{\*\cs16\f0\fs18\cf1\Protect\StyleNameReadOnly ReadOnly;}{\*\cs17\f0\fs18\cf15\Protect\StyleNameVisitePlanifie VisitePlanifie;}}' +  CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + '\pard\s0\fi0\li0\ql\ri0\sb0\sa0\itap0 \plain \f0\fs18\StyleNameNormal Hello!! This world are so cute !! These tell good morning to all !!\par}');

select * from mytable where contains(memofield,'Hello World');  // Return the second record

select * from mytable where contains(memofield,'"Hello World"');  // Return nothing

select * from mytable where contains(memofield,'*"Hello World"*');  // Return the first and second records

I create an FTS index with and I add delimiters \

select * from mytable where contains(memofield,'"Hello World"');  // Return the first record

But if I search an partial text

select * from mytable where contains(memofield,'"Hello Worl"*');  // Return nothing

select * from mytable where contains(memofield,'"Hello Worl*"');  // Return nothing

select * from mytable where contains(memofield,'*"Hello Worl"*');  // Return the first and second records

Is it possible to search partially and the result set return only the first record?

Best regards

link

answered 13 Oct '15, 18:00

Marius%20Cere's gravatar image

Marius Cere
161182025
accept rate: 0%

No one has an idea of what I could do

(26 Oct '15, 16:56) Marius Cere
Your answer to the original question.
If responding to a request for additional information, please edit the question or use the comment functionality.
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×325
×93

Asked: 08 Oct '15, 19:06

Seen: 3,224 times

Last updated: 26 Oct '15, 16:56

Advantage Developer Zone Contact Us Privacy Policy Copyright Info


Powered by Advantage Database Server and OSQA
Disclaimer: Opinions expressed here are those of the poster and do not necessarily reflect the views of the company.