Skip to Content
0

SQL CONTAINS Exact Search

Oct 21, 2016 at 03:51 PM

137

avatar image

I use Advantage Database Server 10.10.0.49


We have a problem when using CONTAINS in WHERE.

CREATE TABLE "MyTable" (Code AutoInc, Note Memo);
INSERT INTO "MyTable" VALUES(1, 'Test note');
INSERT INTO "MyTable" VALUES(2, 'Test another note');

When I execute this SQL, all records of MyTable return.

SELECT * FROM "MyTable" WHERE CONTAINS(Note, '*"Test Note"*')

Is it possible to use CONTAINS in SQL to obtain only one record when I searched 'Test Note'?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

6 Answers

Marius Cere Nov 03, 2016 at 02:46 PM
0

Anybody have a solution or suggestion?

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Nov 04, 2016 at 06:40 AM
0

I don't know how use CONTAINS for your task, but you can use LIKE or POSITION for it

SELECT * FROM "MyTable" WHERE UPPER(Note) LIKE '%TEST NOTE%'

SELECT * FROM "MyTable" WHERE POSITION('TEST NOTE' IN UPPER(Note))>0

But in this case, the expression is case-sensitive.

Share
10 |10000 characters needed characters left characters exceeded
Marius Cere Nov 04, 2016 at 12:08 PM
0

I use the CONTAINS because the field we use have an FTS index for optimize the performance of the SQL

Show 1 Share
10 |10000 characters needed characters left characters exceeded

using a * as a leading character in a search can't be optimized by any index ... not even a FTS index.

0
avatar image
Former Member Nov 04, 2016 at 12:31 PM
0

Sorry Marius, I don't know how to help you.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thanks Igor

0
avatar image
Former Member Nov 05, 2016 at 08:23 AM
0

I think you can use the temporary table

try DROP TABLE #temp; ctach all end try;
SELECT * INTO #temp FROM"MyTable" WHERE CONTAINS(Note, '*"Test Note"*');
SELECT * FROM #temp WHERE UPPER(Note) LIKE '%TEST NOTE%'

I think it works properly and fast.

Share
10 |10000 characters needed characters left characters exceeded
Michael Loop
Nov 16, 2016 at 07:03 PM
0

Marius,

When I tested your query in ADS 12.0, I got the same results as you. However when I removed the asterisks, i.e.

SELECT * FROM "MyTable" WHERE CONTAINS(Note, '"Test Note"')

only the first row of the table was returned.

The asterisk is normally used on a single word for substring matches. For example, *lock* matches 'lock'. 'locked', 'unlock', 'unlocking', etc. I can not find an example where appending an asterisk to an exact phrase would be appropriate. Can you explain what you intended to use the asterisks for?

If my syntax does not work for you, can you tell me what version of ADS you are using?

Regards,

Mike Loop - SAP Product Support

Share
10 |10000 characters needed characters left characters exceeded