SQL Server dynamic column list in contains function

I have a database table in SQL Server 2008 with 5 nvarchar(max) columns. We're using the CONTAINS function to look for text in these columns.

We can look in all five columns using this kind of query:

SELECT *
FROM SomeTable ST
WHERE CONTAINS( (ST.ColumnA, ST.ColumnB, ST.ColumnC, ST.ColumnD, ST.ColumnE) , '"Strawberry"')

Now we want to search for text for one or more of these columns dynamically. For example, only look in ColumnB and ColumnE. I tried using a CASE statement, but I couldn't.

The only solution I can think of is dynamic SQL, but I'd prefer to avoid this. (The full query is very complicated.) Is there a way to do this without using dynamic SQL?

13.10.2009 21:44:01
3 ОТВЕТА

I just can think in a solution like this, but this doesn't use full text Search... let me know if it is useful for you.

SELECT *
FROM SomeTable ST
WHERE 1=1
AND ((@colA = '') OR ST.ColumnA LIKE @colA)
AND ((@colB = '') OR ST.ColumnB LIKE @colB)
AND ((@colC = '') OR ST.ColumnC LIKE @colC)
AND ((@colD = '') OR ST.ColumnD LIKE @colD)
AND ((@colE = '') OR ST.ColumnE LIKE @colE)

The search_on parameters would be bit (0, 1) so, when set on 1, the search on that column would be activated.

0
13.10.2009 21:58:20
That doesn't use fulltext search which contains does.
HLGEM 13.10.2009 21:51:18
If you are thinking SQL is going to shortcut the logic, wouldn't you use 'and' clauses instead or 'or' so the first clause being false created the shortcut?
Andrew 13.10.2009 21:56:02
the OR inside the parenthesis would tell SQL that if first parameter is empty don't check the second condition, and so forth for each "AND" line
Jhonny D. Cano -Leftware- 13.10.2009 21:59:26
Yeah can see your logic with the empty strings over the original numeric comparison. It will short circuit and is simple enough that the evaluation order is unlikely to change.
Andrew 13.10.2009 22:14:16
Actually this is the solution we have been using. The problem is boolean searches. If you search for "strawberries AND bananas", it will only find those with both words in a single column. If "strawberries" is in ColumnA and "bananas" is in ColumnB, then it won't return the record.
david.mchonechase 14.10.2009 01:27:50

The only way I can think of to avoid using dynamic SQL involves using a temp table and if statements and stored proc. Have a stored proc with parameters for each of the columns that include the text to search that columns for. Create a temp table or table variable to store interim results.

Have five different if statements one for each possible column. In each if insert to the temp table if the variable is not null. something like:

IF @ColA is not null
BEGIN
INSERT INTO #temp
SELECT * FROM SomeTable ST
        WHERE CONTAINS( (ST.ColumnA) , @ColA)
END

At the end select from the temp table to show your result.

This only works well though if you don't have very many columns and it would be unlikely that more will be added. Frankly, the fact that you have multiple columns you need to do full text search for the same search string indicates to me that you may have a basic problem with the database design.

1
13.10.2009 21:55:45

You could UNION the 5 individual cases, then PIVOT and concatenate. I'm not sure it's any better than dynamic SQL.

You would end up with something like:

SET @FindKey = '%B%E%' -- This is your search which field criteria

WITH RESULTS1 AS (
    SELECT PK, 'A' AS Col
    FROM SomeTable ST
    WHERE @FindKey LIKE '%A%' AND CONTAINS(ST.ColumnA, '"Strawberry"')
    UNION
    SELECT PK, 'B' AS Col
    FROM SomeTable ST
    WHERE @FindKey LIKE '%B%' AND CONTAINS(ST.ColumnB, '"Strawberry"')
    UNION
    SELECT PK, 'C' AS Col
    FROM SomeTable ST
    WHERE @FindKey LIKE '%C%' AND CONTAINS(ST.ColumnC, '"Strawberry"')
    UNION
    SELECT PK, 'D' AS Col
    FROM SomeTable ST
    WHERE @FindKey LIKE '%D%' AND CONTAINS(ST.ColumnD, '"Strawberry"')
    UNION
    SELECT PK, 'E' AS Col
    FROM SomeTable ST
    WHERE @FindKey LIKE '%E%' AND CONTAINS(ST.ColumnE, '"Strawberry"')
)
,RESULTS2 AS (
SELECT PK, ISNULL([A], '') + ISNULL([B], '') + ISNULL([C], '') + ISNULL([D], '') + ISNULL([E], '') AS FoundKey
FROM RESULTS PIVOT ( MIN(Col) FOR Col IN ([A], [B], [C], [D], [E]) ) AS pvt
)
SELECT *
FROM SomeTable
INNER JOIN RESULTS2
    ON RESULTS2.PK = SomeTable.PK
WHERE RESULTS2.FoundKey LIKE @FindKey
0
13.10.2009 22:16:47
I hadn't thought of pivot tables, but I think this would have a problem with boolean searches. If you search for "strawberries AND bananas", it will only find those with both words in a single column. If "strawberries" is in ColumnA and "bananas" is in ColumnB, then it won't return the record. Is that correct?
david.mchonechase 14.10.2009 12:43:32
Yes, that is correct, to do something like that you would have to expand that logic out into the UNION. I really think dynamic SQl is a good option here.
Cade Roux 14.10.2009 15:36:11