SELECT statement SQL Server (full text) similar to "CONTAINS(column, 'term1 or term 2 or term3') AND NOT (anything else)"

Question

I have a full text search going with the following scenario:

I'd like to look for rows containing exactly the terms I have specified, and nothing more.

My column looks like:

        Col1
        apples oranges grapes
        apples oranges
        apples strawberries
        apples strawberries peaches
        apples

But I want to search only for rows that contain apples and/or strawberries, and nothing else

my result will look like:

1| apples strawberries
2| apples

Thank you :)

Ended up answering my problems [here][1] [1]: http://stackoverflow.com/q/12979996/1751090 — Rachael, Nov 16 '12 at 18:44

score 0 · Answer 1 · edited May 23 '17 at 10:34

I'm not really sure where your table1Col1 comes into play here, so the below may not quite be suffiecient to fully solve your problem , but it should get you started:

First you need a split function, such as:

CREATE FUNCTION dbo.Split (@Input NVARCHAR(MAX), @Delimiter NVARCHAR(1))
RETURNS @T TABLE (OutputValue NVARCHAR(MAX))
AS
BEGIN

    WITH CTE AS
    (   SELECT  [OutputValue] = SUBSTRING(@Input, 1, CHARINDEX(@Delimiter, @Input)), 
                [InputValue] = SUBSTRING(@Input, CHARINDEX(@Delimiter, @Input) + 1, LEN(@Input)) + ' '
        UNION ALL 
        SELECT  [OutputValue] = SUBSTRING([InputValue], 1, CHARINDEX(@Delimiter, [InputValue])), 
                [InputValue] = SUBSTRING([InputValue], CHARINDEX(@Delimiter, [InputValue]) + 1, LEN([InputValue]))
        FROM    CTE
        WHERE   LEN([InputValue]) > 0
    )
    INSERT @T
    SELECT  DISTINCT OutputValue
    FROM    CTE

    RETURN;
END

Then you can use something like this:

DECLARE @T TABLE (Col1 NVARCHAR(MAX));
INSERT @T VALUES ('apples oranges grapes'), ('apples oranges'), ('apples strawberries');

DECLARE @StringToCheck NVARCHAR(MAX) = 'apples oranges bananas grapes'

SELECT  Col1
FROM    @T
        CROSS APPLY dbo.Split(Col1, ' ') spl
        LEFT JOIN dbo.Split(@StringToCheck, ' ') chk
            ON chk.OutputValue = spl.OutputValue
GROUP BY Col1
HAVING COUNT(spl.OutputValue) = COUNT(chk.OutputValue)

Or if you need to specifically exclude Items

DECLARE @T TABLE (Col1 NVARCHAR(MAX));
INSERT @T VALUES ('apples oranges grapes'), ('apples oranges'), ('apples strawberries');

DECLARE @StringToCheck NVARCHAR(MAX) = 'apples oranges bananas grapes',
        @StringToExlude NVARCHAR(MAX) = 'strawberries grapes'

SELECT  Col1
FROM    @T
        CROSS APPLY dbo.Split(Col1, ' ') spl
        LEFT JOIN dbo.Split(@StringToCheck, ' ') chk
            ON chk.OutputValue = spl.OutputValue
        LEFT JOIN dbo.Split(@StringToExlude, ' ') exc
            ON exc.OutputValue = spl.OutputValue
GROUP BY Col1
HAVING  COUNT(spl.OutputValue) = COUNT(chk.OutputValue)
AND     COUNT(exc.OutputValue) = 0

score 0 · Accepted Answer · answered Oct 25 '12 at 20:56

Figured it out myself after a lot of playing around: (also edited question to reveal the true problem I was having):

   SELECT * FROM Table WHERE CONTAINS(Col1, 'apples or strawberries')
                           AND (Col1 NOT LIKE '%[^ apples or strawberries ]%');

SELECT statement SQL Server (full text) similar to "CONTAINS(column, 'term1 or term 2 or term3') AND NOT (anything else)"

2 Answers2