SQL CONTAINS Exact Search

[Former Member]

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'?

[michael_loop]

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

[former_member1069968]

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.

[former_member1069968]

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

[Former Member]

Thanks Igor

[Former Member]

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

[joachim_drr]

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

[former_member1069968]

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.

[Former Member]

Anybody have a solution or suggestion?