Skip to Content

SQL CONTAINS Exact Search

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

6 Answers

  • Nov 03, 2016 at 02:46 PM

    Anybody have a solution or suggestion?

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Nov 04, 2016 at 06:40 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 04, 2016 at 12:08 PM

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

    Add comment
    10|10000 characters needed characters exceeded

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

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

    Add comment
    10|10000 characters needed characters exceeded

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

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 16, 2016 at 07:03 PM

    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

    Add comment
    10|10000 characters needed characters exceeded